Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
peschu123
Contributor 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
Contributor III

Re: Timestamp conversion

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,

10 Replies
MVP
MVP

Re: Timestamp conversion

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

MVP
MVP

Re: Timestamp conversion

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
Valued Contributor II

Re: Timestamp conversion

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

peschu123
Contributor III

Re: Timestamp conversion

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
Contributor III

Re: Timestamp conversion

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.

MVP
MVP

Re: Timestamp conversion

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
Contributor III

Re: Timestamp conversion

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,

MVP
MVP

Re: Timestamp conversion

.fff -> fraction to miliseconds

peschu123
Contributor III

Re: Timestamp conversion

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!

Community Browser