Time zone in timestamp?

[ellenblackwell]

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

[thakkarrahul01]

Hi Ellen,

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

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

Regards,

Rahul

[ellenblackwell]

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.

[thakkarrahul01]

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

[rwunderlich]

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