Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Time zone in timestamp?

Ok, I give up. I've searched and attempted many different options for resolving my date/timestamp issue without success. I have the following value:

2018-08-31T19:14:48.651-05:00

Upon extracting the date value, no matter what syntax I have employed (including the Date, Date#, Timestamp, Floor, etc. functions in various syntax positions), the date is returned as Sep 1 instead of Aug 31. What I know about this field is that it is supposed to contain the date and time CST. I would greatly appreciate assistance with the following questions.

1. How best can I most efficiently extract the date 08/31/2018 as [Trans Date]?

2. How best can I most efficiently extract the seconds, 48, as [Trans Seconds]?

As always, I appreciate your assistance!

Ellen

4 Replies
Highlighted
Creator
Creator

Hi Ellen,


Please try floor function for your first question, also see if below thread -


https://community.qlik.com/thread/252288


Regards,

Rahul

Highlighted
Partner
Partner

Thank you Thakkar. I previously tried the FLOOR function and read the referenced thread. I've narrowed down the issue to this part of the format:

2018-08-31T19:14:48 [.651-05:00]

In my SET TimeStampFormat statement,

  • .fff ties to the number 651 (although, I do not know what that number represents)
  • I do not know how to account for the -05:00 in TimeStampFormat syntax, but it appears to be adding 5 hours to the base date/time field.
    • So, 8/31/18 at 7:14p and 48 seconds is becoming 9/1/18 at 12:48a and 48 seconds

Not sure how to ignore the -05:00 in the field or subtract 5 hours from the result.

Highlighted
Creator
Creator

The number 651 represents fractional seconds. And to get the correct date can you try to use function ConvertToLocalTime and apply floor function on top of it. 


More details - 


https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/con...



https://apex.oracle.com/pls/apex//apexcommunity/tipp/6381/index-en.html


Regards,

Rahul

Highlighted
MVP & Luminary
MVP & Luminary

When a time offset is detected, the resulting time is UTC time.

http://qlikviewcookbook.com/2016/06/qv12-timestamp-parsing/

In your example, the 5 hours are added to derive UTC time which makes it the next day.  You can convert using the ConvertTolocalTime() function as Rahul suggested.  Or if you really only want the date portion, use just the date portion of the string when you load it.

Date(Date#(left(myfield, 10), 'YYYY-MM-DD')) as TranDate

-Rob