Timestamp conversion

[peschu123]

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

[tresesco]

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]

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]

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

[peschu123]

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]

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]

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 · Accepted solution]

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]

.fff -> fraction to miliseconds

[peschu123]

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!