topic Re: Timestamp conversion in QlikView

Timestamp conversion

peschu123 — Fri, 16 Aug 2013 09:08:54 GMT

Hi,

I have the following field in my database:

[Date/Time] with format YYYY/MM/DD hh:mm PM --> "2012/09/17 11:31 PM"

First I want to convert this to:

[Date/Time] with format: DD.MM.YYYY hh:mm --> ""17.09.2012 23:31"

Further I have to adjust the time from Eastern Time (GMT -4) to GMT + 2 (+6 hours). Is there a function for that. Or do I have to calculate it by extracting the hour and adding 6 hours?

After that I want to extract year,month,day, time(hour would be enough).

I have to admit I have some problems understanding the syntax of the date(formatting) functions. Any help is appreciated.

Regards,

Peter

Re: Timestamp conversion

tresB — Fri, 16 Aug 2013 09:15:03 GMT

From Help:

LocalTime([timezone [, ignoreDST ]])

Returns a timestamp of the current time from the system clock for a specified time zone. The timezone is specified as a string containing any of the geographical places listed under Time Zone in the Windows Control Panel for Date and Time or as a string in the form 'GMT+hh:mm' or 'UTC+hh:mm'. If no timezone is specified the local time will be returned. If ignoreDST is -1 (true) daylight savings time will be ignored.

Examples:

localtime ('Paris')

localtime ('GMT+01:00')

localtime ('Paris',-1)

localtime()

Re: Timestamp conversion

swuehl — Fri, 16 Aug 2013 09:15:51 GMT

You should use Timestamp#() function to parse your string into a QV timestamp with a numeric representation.

You can use ConvertToLocalTime() to adjust your timezone and then use Hour(), Day(), Month(), Year() function to retrieve the information you want.

Use Timestamp() function to format your timestamp to a different format.

Re: Timestamp conversion

paulyeo11 — Fri, 16 Aug 2013 09:21:01 GMT

interesting to see 2 master have different answer on same question.

Re: Timestamp conversion

peschu123 — Fri, 16 Aug 2013 09:54:55 GMT

I don't know in which way the excerpt from the help should help me.

Especially when I already said that I don't really get it with those functions. This further implies that I have already used the help...

Re: Timestamp conversion

peschu123 — Fri, 16 Aug 2013 10:11:53 GMT

Hi swuehl,

thank you for your help.

This was enough to lead me on the right path. I hoped for an example but I got it quite well.

I used this to create my timestamp:

timestamp(Timestamp#([Date/Time],'YYYY/MM/DD hh:mm TT' ),'DD.MM.YYYY hh:mm') as timestamp,

One question the expression above with just timestamp# generated a numeric value, easiest to see by the orientation in a table.(left is default for strings and right for numeric values). But when I look on table viewer preview, I just see the normal timestamp as string?

thank you for your help.

Re: Timestamp conversion

tresB — Fri, 16 Aug 2013 10:17:34 GMT

Hello Peter,

Try as following:

Timestamp([Date/Time] , 'DD.MM.YYYY hh:mm' )

Then to add 6 hours :

Load

TimeStamp([Date/Time] + Time#(6,'hh') , 'DD.MM.YYYY hh:mm') as LocalTime // gives your local time

Now use Hour(), Month(), Year() functions on this LocalTime.

Hope this helps.

Re: Timestamp conversion

peschu123 — Fri, 16 Aug 2013 10:31:37 GMT

Hi again,

thank you too tres qv for the help. I was a bit confused by the functions or a bit afraid, but it seems to be pretty easy.

I use the following expression to directly show the me the correct result:

ConvertToLocalTime(timestamp(Timestamp#([Date/Time],'YYYY/MM/DD hh:mm TT' ),'DD.MM.YYYY hh:mm'),'GMT+06:00') as timestamp,

I have to use GMT+06:00 because QV assumes that the timestamp is gmt?! I don't know how to change this.

What means [.fff] in timestampformat? Sometimes the help isn't that helpful it should..

BUT thank you all for your fast help.

EDIT: It even works perfectly without timestamp(). But I think in some cases it could be an advantage to define the format.

The formula than is:

ConvertToLocalTime(Timestamp#([Date/Time],'YYYY/MM/DD hh:mm TT' ),'GMT+06:00') as timestamp,

Re: Timestamp conversion

tresB — Fri, 16 Aug 2013 10:36:22 GMT

.fff -> fraction to miliseconds

Re: Timestamp conversion

peschu123 — Fri, 16 Aug 2013 10:49:50 GMT

Ok tres qv,

And sorry for my first reply to your post, I interpreted that as something like "RTFM!".

But I read my initial post again and now I think it just should be a hint to this function.

Have nice day and thank you!

Re: Timestamp conversion

tresB — Fri, 16 Aug 2013 10:54:46 GMT

, It's alright, you have got the solution, that is more important. Have a nice day.