Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ysj
Creator
Creator

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

Hi,

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

timestamp.png

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.

12 Replies
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
Creator
Creator
Author

Thank you Ruben,

screen.png

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
Creator
Creator
Author

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

ysj
Creator
Creator
Author

in your example is adding 4 hours

antoniotiman
Master III
Master III

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

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
Creator
Creator
Author

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