Convert BIGINT Timestamp to a Datetime


  • We have a Blackberry Enterprise Server database that we'd like to do some reporting on. Two of the tables have a field called TIMESTAMP, but the datatype of the columns are not TIMESTAMP or any date datatype - they are BIGINT's. They consist of 13 characters. For example:


    Is there any way to convert this to an actual date?



    A. M. Robinson

    8 июня 2012 г. 22:49


Все ответы

  • select dateadd(second, 1334034120644 /1000 + 8*60*60, '19700101')

    This was the first link I found, although this topic was discussed in this forum also

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    • Помечено в качестве ответа ansonee 19 июня 2012 г. 14:45
    8 июня 2012 г. 22:56
  • My guess is that the datetime format you mentioned is a Unix format, where it represents the number of milliseconds since Jan 1 1970 12:00:00 AM.  You'll want to use the DATEADD function to do the conversion.

    Sample code and further discussion can be found here:

    Sam Lester (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    8 июня 2012 г. 23:00
  • Small modification so that the seconds do not get lost:

    select dateadd(second,
        1334034120644 /1000 + 1334034120644 % 1000 + 8*60*60, '19700101')

    As for the 8*60*60 thing that is a time-zone adjustment. Unix times are in UTC, so the above gives the timestamp in Chinese time.

    Erland Sommarskog, SQL Server MVP,
    9 июня 2012 г. 8:48
  • Naomi:

    Worked great! One question I have, would I adjust the query to return the time in CST United States?

    We need the time to be the time in Chicago, IL.


    A. M. Robinson

    9 июня 2012 г. 16:58
  • Worked great! One question I have, would I adjust the query to return the time in CST United States?

    We need the time to be the time in Chicago, IL.

    Chicago has the nasty habit of changing the time twice a year in observance of daylight savings time.  To convert a UTC datetime to local time easily, you'll need to use a SQLCLR function.  See  The example below shows use of the CLR assembly function from that thread:

    CREATE FUNCTION dbo.UtcToLocalTime(@UtcDateTime datetime)
    RETURNS datetime
    AS EXTERNAL NAME [SqlClrDateTime].[UserDefinedFunctions].[displayLocalTime];
    SELECT dbo.UtcToLocalTime('2012-03-11T07:59:59.000'); --2012-03-11T01:59:59.000 Central Standard Time
    SELECT dbo.UtcToLocalTime('2012-03-11T08:00:00.000'); --2012-03-11T03:00:00.000 Central Daylight Time
    SELECT dbo.UtcToLocalTime('2012-11-04T06:59:59.000'); --2012-11-04 01:59:59.000 Central Daylight Time
    SELECT dbo.UtcToLocalTime('2012-11-04T07:00:00.000'); --2012-11-04 01:00:00.000 Central Standard Time

    Dan Guzman, SQL Server MVP,

    • Предложено в качестве ответа Naomi NModerator 10 июня 2012 г. 3:02
    • Отменено предложение в качестве ответа ansonee 11 июня 2012 г. 15:08
    9 июня 2012 г. 17:31
  • Ideally, what we'd like to do is just add a new computed column to the tables that will just compute the datetime on the fly. Unfortunately, this database is running on SQL Express 2005, and no one here has a whole lot of experience with CLR.

    Using your CLR solution, we would need to convert the UNIX timestamp to a DATETIME first, then use your CLR function to convert to local...

    Is there another way to convert to local time without having to use CLR? I thought just adding six hours to the first converted timestamp should suffice. We really don't need to worry too much about DST in this case

    A. M. Robinson

    11 июня 2012 г. 18:27
  • Assuming that you are using 2008+, you can cast the UTC date to an offset and then use switchoffset to -5 (CST) and that will give you the local time.

    SELECT CAST(SWITCHOFFSET(CAST(DATEADD(SECOND,1334034120644 /1000 + 1334034120644 % 1000 + 8*60*60, '19700101') AS DATETIMEOFFSET),'-05:00') AS DATETIME2)

    11 июня 2012 г. 19:01
  • If you don't care about DST, you can get the desired value by subtracting six hours. If you add six hours you will get a time zone in Asia.

    You could also add a CASE expression that covers the rules for Illinois to subtract five or six hours depending on the time of the year.

    Erland Sommarskog, SQL Server MVP,
    11 июня 2012 г. 22:08
  • Oops!!! You're right...! SUBTRACT six hours...!

    A. M. Robinson

    12 июня 2012 г. 15:37
  • We're actually using SQL Express 2005 unfortunately...

    A. M. Robinson

    12 июня 2012 г. 16:25
  • Just try 

    SELECT DATEADD(HOUR, 7, DATEADD(S ,1334034120644/1000, '1970-01-01')) -- HOUR, 7 ==> UTC+7

    You can modify UTC + X approriate with your area

    25 апреля 2013 г. 7:54