How can I convert a timestamp into another timezone

[#1]

For performance and storage reasons we store timestamp values in two separate columns as written here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/23/the-master-time-table

The data we need to load is stored inside a QVD file (incremental load into those QVD files, and afterwards load all data with a query from those files). The QVD contain two column "Date" and "Time" with a split timestamp information. The data contains UTC timestamps in e. g. the following style

Time (in UTC)	Date (in UTC)
11:35	12/12/2014
13:56	12/12/2014
and so on...

Inow want to shift data to match a specific timezone but keep the QVD files unchanged (e. g. shift to GMT-10:00 to keep it easy).

Because the time and date information is splitted I need to combine them again.

     Timestamp#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm') AS "Parsed Timestamp",

Should do the trick. And the datamodell shows it works: "12/12/2014 11:35" is shown in the table preview.

The load script:

LOAD *,

    Timestamp#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm') AS "Parsed Timestamp",

    Date#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm') AS "Parsed Date",

    ConvertToLocalTime(Timestamp#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm'), 'UTC-10:00') AS "Shifted Timestamp",

    ConvertToLocalTime(Date#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm'), 'UTC-10:00') AS "Shifted Date",

    ConvertToLocalTime('2007-11-10 23:59:00','UTC-10:00') AS "example 1 from documentation",

    ConvertToLocalTime(UTC(),'UTC-10:00') AS "example 2 from documentation"

    FROM '$(QvdConnection)/someQvdFile.qvd' (qvd)

    WHERE "Application ID"='$(ApplicationId)';

Table preview

The output in the datamodell table preview will look like this

Time	Date	Parsed Timestamp	Parsed Date	Shifted Timestamp	Shifted Date	example 1 from documentation	example 2 from documentation
11:35	12/12/2014	12/12/2014 11:35	12/12/2014 11:35	-	-	11/10/2007 1:59:00 PM	12/12/2014 2:46:13 AM
13:56	12/12/2014	12/12/2014 13:56	12/12/2014 13:56	-	-	11/10/2007 1:59:00 PM	12/12/2014 2:46:13 AM
...

The Timestamp# and Date# methods return something, and it looks correct (although the AM information is not there).

The ConvertToLocalTime method does work with UTC-10:00, but with GMT-10:00 you get the same results (the examples on in the documentation work).

Why does the ConverToLocalTime method ignore my timestamp or date input?

What do I have to change to get this working?

Thanks a lot for helping me out 🙂

Thilo

[marcus_sommer]

I assume the reason is that ConvertToLocalTime couldn't interpret the input properly. Maybe you creates the same format like your manually example or you wrapped your timestamp#() into a timestamp().

- Marcus

[#3]

How can I see if the ConvertToLocalTime method could not parse the input? Is there any logfile I can have a look at? or something I could see in debug mode? For me it is like a blackbox with to less information on error behavior.

This extends to timestamp#, I assume that if the table preview show a value there the method was successful. And the documentation of ConvertToLocalTime expects a "timestamp" as first argument, so this should be fine, isn't it?

But I'll try the timestamp(timestamp#( ...) ) approach, although it looks like wasting resources and bad coding style 🙂

[marcus_sommer]

I suggest you tried it first in a qv textbox with one or two original values manually and if this worked it should work in script, too.

- Marcus

[#5 · Accepted solution]

I tackled the problem. It was a wrong date format...

When using the correct date format, everything works

Now it only depends on your flavor, if you want to use a date a timestamp, wrap the parsing call into a formattings call or use even time formating

LOAD *

,ConvertToLocalTime(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'UTC-10:00') AS "Shifted Timestamp"

,ConvertToLocalTime(Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'UTC-10:00') AS "Shifted Date"

,ConvertToLocalTime(Timestamp(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm')), 'UTC-10:00') AS "Shifted Timestamp from Timestamp"

,ConvertToLocalTime(Date    (Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'YYYY-MM-DD hh:mm:ss'), 'UTC-10:00') AS "Shifted Date from Date"

,ConvertToLocalTime(Date    (Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm')), 'UTC-10:00') AS "Shifted Date from def Date"

,ConvertToLocalTime(Time(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm')), 'UTC-10:00') AS "Shifted Time from Timestamp"

,ConvertToLocalTime(Time(Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'YYYY-MM-DD hh:mm'), 'UTC-10:00') AS "Shifted Time from Date"

    FROM '$(QvdConnection)/someQvdFile.qvd' (qvd)

    WHERE "Application ID"='$(ApplicationId)';

They all return the same value, that the reason why I will use ,ConvertToLocalTime(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'UTC-10:00') AS "Shifted Timestamp" because it is simple and does the trick.