Post reply

Storing Unix Date/Time

  • July 26, 2005 at 8:25 am

    #68230

    I need to store Unix Date/Time in one of the columns of a table. What datatype should I use to store the Unix Date/Time datatype? I may need to perform sql datetime operations on this data at a later point.

  • July 26, 2005 at 8:38 am

    #577185

    Why not store it in a datetime field? Does SQL not recognize it as datetime  and implicitly convert it? What does the unix output look like?

  • July 26, 2005 at 9:15 am

    #577209

    Good day Sarah. I have the very same questions!

  • July 27, 2005 at 12:45 am

    #577435

    Unix Date/Time is represented as the number of seconds that have passed since 1970-01-01. So you could of course simply store it as an int, but then you would need to convert it into a datetime if you needed to apply datetime functionality to it. As Sara said, why not simply store it in a datetime column from the start, and do the conversion when storing or retrieving the data?

    CREATE TABLE FOO (bar DATETIME)

    INSERT INTO FOO VALUES (DATEADD(s, 1122452850, '1970-01-01'))

    SELECT bar, DATEDIFF(s, '1970-01-01 00:00:00', bar) AS unixtime FROM FOO

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • July 27, 2005 at 9:01 am

    #577587

    Do yourself a favor and do not store you date in Unix Date/Time. I am currently having to design reports out of a system that is Unix Date/Time and the grief I have to go through to deal with converting furture and present time zones is dumb. Anything saved in storing data in this manner is lost in processing time of time zones.

  • July 27, 2005 at 9:20 am

    #577602

    Thanks all. But the client requires that the date/time be stored in the Unix format. I am currently using the numeric datatype. Which would be better or more applicable, integer or numeric datatype?

  • July 27, 2005 at 9:32 am

    #577608

    Bigint in case they want miliseconds from 01/01/1970. Whats you client building/selling so I can avoid it in the future?

  • July 28, 2005 at 12:55 pm

    #577990

    I just want to add ...

     

    In SQL:

     select getdate()

    produces:

     2005-07-28 13:33:18.107

    In Unix:

     date

    by default produces:

     Thu Jul 28 13:30:18 CDT 2005

    There are formatting commands available to produce a date similar to SQL Server that will enable you to take the text from the 'date' command and convert it to a SQL Server datetime variable with virtually no effort.

    Now again:

     date '+%Y-%m-%d %H:%M:%S:000'

    now produces:

     2005-07-28 13:35:58:000

    Remarkably similar ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply