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?


      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.








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















      /* 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?

          Try this:

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



          Sanjay Dhonde

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

              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:



                  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?

                      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;



                          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;



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

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

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


                          Good question!