Convert Unix Timestamp into MS SQL datetime
Results 1 to 4 of 4

Thread: Convert Unix Timestamp into MS SQL datetime

  1. #1
    Junior Member
    Join Date
    Nov 2006
    Posts
    5

    Convert Unix Timestamp into MS SQL datetime

    I'm trying to convert a Unix timestamp in a format to insert into a MS SQL datetime column (2007-05-03 16:38:04.620). Any ideas? I can insert the current datetime with getdate() but I have not been able to find out how to convert a Unix timestamp into the correct format.

  2. #2
    Senior Member
    Join Date
    Jan 2002
    Posts
    2,126
    It's easy. A Unix timestamp is the number of seconds since 01/01/1970. So just add the timestamp number of seconds to that date:
    PHP Code:
    declare @UNIX_TIMESTAMP int
    select 
    @UNIX_TIMESTAMP 1178225057
    select  dateadd
    (ss,@UNIX_TIMESTAMP,'01/01/1970') as theDateTime

    select datediff
    (ss'01/01/1970'getdate()) as theTimestamp 

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,943
    If you want to do it via PHP:
    PHP Code:
    $sqlDateTime date('Y-m-d H:i:s'$unixTimeStamp); 
    Then use $sqlDateTime in your SQL:
    PHP Code:
    $sql "INSERT INTO table_name (date_time_column) VALUES ('$sqlDateTime')"
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  4. #4
    Senior Member Roger Ramjet's Avatar
    Join Date
    Jul 2004
    Location
    Leeds, UK
    Posts
    4,203
    Is this MS SQL or MySQL?

    In mysql you have the sql function FROM_UNIXTIME() to do it for you in the query
    PHP Code:
    $sql "INSERT INTO table_name (date_time_column) VALUES (FROM_UNIXTIME($unixTimeStamp))"
    I'm not that up on MS SQL functions but guess there may be an equivalent.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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