10 Replies Latest reply: Aug 16, 2013 6:54 AM by Tresesco B RSS

    Timestamp conversion

    Peter Schulz

      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
          Tresesco B

          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
              Peter Schulz

              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
                  Tresesco B

                  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
                Stefan Wühl

                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
                    Peter Schulz

                    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
                    PAUL YEO

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

                    • Re: Timestamp conversion
                      Peter Schulz

                      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,