Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peschu123
Partner - Creator III
Partner - Creator III

Timestamp conversion

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

1 Solution

Accepted Solutions
peschu123
Partner - Creator III
Partner - Creator III
Author

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,

View solution in original post

10 Replies
tresesco
MVP
MVP

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()

swuehl
MVP
MVP

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.

paulyeo11
Master
Master

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

peschu123
Partner - Creator III
Partner - Creator III
Author

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

peschu123
Partner - Creator III
Partner - Creator III
Author

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.

tresesco
MVP
MVP

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.

peschu123
Partner - Creator III
Partner - Creator III
Author

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,

tresesco
MVP
MVP

.fff -> fraction to miliseconds

peschu123
Partner - Creator III
Partner - Creator III
Author

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!