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

[ysj]

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.

[rubenmarin]

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

[ysj]

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.

[rubenmarin]

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))

[ysj]

hicstevedarkmarkodonovanstalwar1shane_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‌

[ysj]

in your example is adding 4 hours

[antoniotiman]

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

[rubenmarin]

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.

[stevedark]

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

[ysj]

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,

YJ