6 Replies Latest reply on Feb 13, 2014 7:58 AM by pradeepgm Branched from an earlier case.

    How can I convert BigInt dates into timestamps?

    allanlewis67

      Hi Ed Snajder

       

      1) I was looking for a good place here to post a SQL query I wrote.  When I search for SQL or database there doesn't seem to be a Place related to this.  I fouind some queries in the https://community.jivesoftware.com/groups/uk-user-group but that's not ideal.

       

      Would you consider arranging for one, or who should I contact?

       

       

      2) I toyed once with a query to try to convert the BigInt type dates into dates I could understand, but did not succeed. Yet.  But since we're nboth here, do you have sql code to do this? Below is my attempt.

       

      Thanks,

      Allan

       

       

      Select

       

      dateadd(S, modificationDate, '1970-01-01'

       

      )

       

       

       

      From

       

       

      [jivelaheycore].[dbo].

       

      [jiveDocVersion]

       

       

       

      /* http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server */

       

       

       

       

       


        • Re: How can I convert BigInt dates into timestamps?
          sanjayd

          Try this:

          select TO_TIMESTAMP(u.creationdate / 1000) from jiveuser u ;

           

          Thanks,

          Sanjay Dhonde

            • Re: How can I convert BigInt dates into timestamps?
              allanlewis67

              Thank you Sanjay.

               

              When I ruun both your exact query

              and one of mine where I refer to creationdate in another table with it,

              I receive following error:

              Msg 195, Level 15, State 10

               

              'TO_TIMESTAMP' is not a recognized built-in function name.

               

               

               

              This is what I'm using with SQL Server 2012:

               

              Microsoft SQL Server Management Studio      11.0.2100.60

              Microsoft Analysis Services Client Tools      11.0.2100.60

              Microsoft Data Access Components (MDAC)      6.1.7601.17514

              Microsoft MSXML      3.0 4.0 6.0

              Microsoft Internet Explorer      8.0.7601.17514

              Microsoft .NET Framework      4.0.30319.1008

              Operating System      6.1.7601

               

              Any quick thoughts?

                • Re: How can I convert BigInt dates into timestamps?

                  The epoch stamps in the tables use ms precision, but MSSQL doesn't have a to_timestamp function, so:

                  SELECT

                  creationdate,

                  dateadd(s,modificationDate/1000,'1970-01-01') as datetimesec

                  FROM jivedocversion

                  will work. Hope that helps!

                    • Re: How can I convert BigInt dates into timestamps?
                      pradeepgm

                      Hi Ed Snajder

                       

                      What is the suggested method for PostgresSql.

                       

                      For example, to retrieve creationdate or modificationdate, we normally use TIMESTAMP 'epoch' + creationdate * interval '1 millisecond' as creationdate.

                       

                      Is that correct approach? How do we consider timezone as well as here?

                       

                      Pradeep GM

                        • Re: How can I convert BigInt dates into timestamps?

                          Hi Pradeep!

                          Usually, if the ms is not critical for my analysis, I will use to_timestamp(creationdate/1000). This will include the TZ. For exmaple, if I take a sample of dates using your method (which is a great method when you do want to consider ms), the result set is:

                           

                          # SELECT TIMESTAMP 'epoch' + creationdate * interval '1 millisecond' as result FROM jivedocument LIMIT 3;

                                   result         

                          -------------------------

                          2013-02-22 13:59:54.988

                          2013-02-22 17:16:32.259

                          2013-02-22 14:30:05.105

                           

                          With to_timestamp the local time and offset are included:

                           

                          # select to_timestamp(creationdate/1000) FROM jivedocument limit 3;

                                to_timestamp     

                          ------------------------

                          2013-02-22 05:59:54-08

                          2013-02-22 09:16:32-08

                          2013-02-22 06:30:05-08

                           

                          Good question!