Storing Unix Date/Time

Question

Storing Unix Date/Time

Cory Slaney

SSCrazy

Points: 2459
More actions
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.

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

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

sara karasik SSCommitted Points: 1573 More actions · Answer 1

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?

Cory Slaney SSCrazy Points: 2459 More actions · Answer 2

Cory Slaney

SSCrazy

Points: 2459

July 26, 2005 at 9:15 am

#577209

Good day Sarah. I have the very same questions!

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

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/

bnordberg SSCertifiable Points: 5480 More actions · Answer 4

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.

Cory Slaney SSCrazy Points: 2459 More actions · Answer 5

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?

bnordberg SSCertifiable Points: 5480 More actions · Answer 6

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

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."