topic Re: How to fetch date,time,hour,sec From Timestamp? in QlikView

How to fetch date,time,hour,sec From Timestamp?

ysj — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have differents log files contains timestampas field.in that field I am getting below value.

But I am trying to fetch values date,time,hour,min,sec

Also I have chagned my mai script as below;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='₹ #,##0.00;₹ -#,##0.00';

SET TimeFormat='hh:mm:ss';

//SET DateFormat='YYYY-MM-DD';

//SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-IN';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Please provide your thoughts.

Re: How to fetch date,time,hour,sec From Timestamp?

rubenmarin — Sat, 17 Jun 2017 10:43:40 GMT

Hi, I have set the same formats for date and timestamps formats, my results are:

Timestamp('20170531T000101.00-0400') -> 05/31/2017 4:01:01

Date('20170531T000101.00-0400') -> 05/31/2017

Time('20170531T000101.00-0400') -> 4:01:01

Hour('20170531T000101.00-0400') -> 4

Minute('20170531T000101.00-0400') -> 1

Second('20170531T000101.00-0400') -> 1

Try replacing '20170531T000101.00-0400' for your FieldName

Re: How to fetch date,time,hour,sec From Timestamp?

ysj — Sat, 17 Jun 2017 11:09:29 GMT

Thank you Ruben,

I have used functions that but it seems that not geeting expected result.

It should be like this:

Example 1:

Timestamp('20170531T000101.00-0400') -> 05/31/2017 4:01:01

Date('20170531T000101.00-0400') -> 05/31/2017

Time('20170531T000101.00-0400') -> 12:01:01

Hour('20170531T000101.00-0400') -> 12

Minute('20170531T000101.00-0400') -> 1

Second('20170531T000101.00-0400') -> 1

Example 2:

Timestamp('20170531T164055.00-0400') -> 05/31/2017 4:01:01

Date(''20170531T164055.00-0400') -> 05/31/2017

Time('20170531T164055.00-0400') -> 16:40:55

Hour('20170531T164055.00-0400') -> 16 or 4

Minute('20170531T164055.00-0400') -> 40

Second('20170531T164055.00-0400') -> 55

but when I used your functions it shows something weird values.

I have also atttched the excel in main thread.

Please find it and help me.

Re: How to fetch date,time,hour,sec From Timestamp?

rubenmarin — Sat, 17 Jun 2017 11:20:04 GMT

Hi,

=Time('20170531T164055.00-0400') for me returns 20:40:55

In both examples is adding 4 hours because the timestamp ends in '-0400'.

One way to keep only until the seconds can be using Left() to keep the first 15 characters:

=Time(left('20170531T164055.00-0400', 15))

Re: How to fetch date,time,hour,sec From Timestamp?

ysj — Sat, 17 Jun 2017 11:34:14 GMT

hic stevedark markodonovan stalwar1 shane_spencer@@@@@@@@@@@@@@@@@@@Thank you so much Rubin.

Could you please explain me in detail that what is happing.

I didnt get that timestamp field that its adding 4 hours how and why?

means the timestamp ends in '-0400'. i dont know about my field please explain me.

Also Explain me then which values are correct.

added 4 hours our second one that you written right now?

Try1:

Shall I used below value and why? as you told me that it added four hours because the timestamp ends in '-0400'.

Time('20170531T000101.00-0400') -> 4:01:01

Try 2:

Shall I used below value and why? as you told me that it added four hours because the timestamp ends in '-0400'.

Plese expliane me

=Time(left('20170531T164055.00-0400', 15)) ->16:40:50

Now I am getting confused please provide your thoughts

stalwar1‌ stevedark‌markodonovan‌hic‌ jagan‌

Re: How to fetch date,time,hour,sec From Timestamp?

ysj — Sat, 17 Jun 2017 14:41:32 GMT

in your example is adding 4 hours

Re: How to fetch date,time,hour,sec From Timestamp?

antoniotiman — Sat, 17 Jun 2017 15:34:51 GMT

Hi,

Do You use QV 12.

QV12 supports Timestamp ISO Format. From Help :

format

String describing how the resulting timestamp string is to be formatted. If omitted, the short date format, time format, and decimal separator set in the operating system is used. ISO 8601 is supported for timestamps.

However , see Attachment (QV < 12).

Regards,

Antonio

Re: How to fetch date,time,hour,sec From Timestamp?

rubenmarin — Sat, 17 Jun 2017 20:42:11 GMT

That -0400 is the relative time to UTC, so it says that this time is 4 hour hour less (-0400=-04:00 in format 'hhmm') that the UTC time, as Antonio said, look for ISO 8601 for better explanation.

I think that the one that adds 4 hours it's usually better for analysis, that's the real local time where it happens.

Re: How to fetch date,time,hour,sec From Timestamp?

stevedark — Sun, 18 Jun 2017 06:59:19 GMT

Hi,

In cases where you have data from multiple timezones you may want to carry both local and UTC time, or local and head office time.

If you take the example of work done during shifts, for some analysis you may want to know how your staff are balanced around the globe - so you can see that whilst one region sleeps another is working - for this you would want to convert all times to a consistent time - like UTC. For another analysis you may want to see what time staff are arriving for work, this only makes sense if you only look at local time; work patterns will be comparable in different time-zones and you can chart a working day.

I've not opened antoniotiman‌ 's example, but personally I will always use a Date# function to convert from a date or a time-stamp when loading from a text file, as otherwise changes to system locale (perhaps when moving the QVW or load script between machines) could cause the load to behave differently.

Steve

Re: How to fetch date,time,hour,sec From Timestamp?

ysj — Mon, 19 Jun 2017 10:54:28 GMT

Thank you so much stevedark‌,

As I am using QV12 and my server is also QV12 then what should I do for time format

But this values are comes from server itself then which value is correct either added + 4hours or as it is?

How I can fetch then now from my loaded TimeStamp values .

Could you please provide your expression to fetch date,time,hour,seconds etc

Thank you,

Re: How to fetch date,time,hour,sec From Timestamp?

ysj — Mon, 19 Jun 2017 10:56:59 GMT

Thank you Antonio.

As I am using QV12 and my server is also QV12 then what should I do for time format.

As I have log files in text format having timestamp filed containing mention values.

then what should I do .

Re: How to fetch date,time,hour,sec From Timestamp?

ysj — Mon, 19 Jun 2017 10:59:03 GMT

Thank you so much Ruben,

As I am using QV12 and my server is also QV12 then what should I do for time format

But this values are comes from server itself then which value is correct either added + 4hours or as it is?

Re: How to fetch date,time,hour,sec From Timestamp?

antoniotiman — Mon, 19 Jun 2017 11:15:24 GMT

Hi,

You can use expressions like my attachment below.

It works also in QV12.