Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unhappy Unanswered: Unix timestamp to DATETIME datatype

    Hi all,

    I have moved a mysql table to SQL Server and the table had an int datatype storing the value of the Unix timestamp. I want to convert this datatype into a DATETIME type in SQL Server.

    Any ideas how I could do it?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you give us an example of what it looks like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    Originally posted by Brett Kaiser
    Can you give us an example of what it looks like?
    Brett,

    Yes, this is one of the records in the mySQL db.

    Username TicketNum DateAsked DateResolved Resolved
    Mel 97370819314852 973708193 973799531 y

    The DateAsked and the DateResolved are calculated using the time() function which returns the UNIX timestamp.

    Thanks.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry for being dense...but what date is that? It it Seconds from some date?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Check this out...

    Does that work for you?

    http://dbforums.com/arch/7/2002/8/448532
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    6

    Re: Unix timestamp to DATETIME datatype

    Try this one:

    SELECT dateadd(ss,973708193,'1/1/1970')

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yup, That's what was in the link...sortta

    Why 1970?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    38
    Originally posted by Brett Kaiser
    Yup, That's what was in the link...sortta

    Why 1970?
    The time() function returns the number of seconds since 1/1/1970 !!

    The dateadd() function works for me. Thanks! Another question, how do I get SQL Server to loop through all records and replace the timestamp value with the output of the dateadd() function?? Also, how do I get the dateadd() function to return only the date and not the time?

    Thanks for all your help

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you said the UNIX timestamp is in a integer column, so you'll need to add a column that is defined as datetime..

    Then it's a simple update

    UPDATE Table SET NewCol = SELECT dateadd(ss,unixCol,'1/1/1970')

    SQL Server stores date and time internally...

    It stores the date as a 4 byte integer and the time as a 4 byte integer...

    The rest is a presentation issue...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •