Convert Unix Timestamp

Question

Convert Unix Timestamp

rayd8

Old Hand

Points: 325
More actions
October 8, 2003 at 11:55 am

#81180

I have a Help Desk program that populates our SQL database with a UNIX timestamp (date and time). From my understanding this timestamp counts the number of seconds from January 1, 1970. I've tried using the DATEADD function to generate the date in SQL Server but it is seven hours off. Has anyone had experience with this and can maybe point me to some documentation? As an example 1058484229 = 07/17/03 04:10 pm in the Help Desk software.

Viewing 5 posts - 1 through 5 (of 5 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

In SQL Server datetime datatype is a two 4-byte integer. The first 4 bytes store the number of days after 1 January 1900 and the second 4 bytes stores the number of milliseconds after midnight.

What type of datatype are you saving the UNIX timestamp as in SQL Server?

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 2

BTW-your UNIX software may be doing more 'behind the scenes' than you are aware of.

For example....Let's say you have a date and time in UNIX....October 26th 2:00 AM.

What will that show in the UNIX timestamp? You could have TWO different entries as Oct 26th is when the clocks are set back from daylight savings time.

So, you have one timestamp that shows 7200 seconds have passed on Oct 26th and you have another that shows that 10800 seconds have passed on Oct 26th. But both are really the same time - Oct 26th at 2AM.

How is SQL Server supposed to interpret those and realize they are both the same date and time?

-SQLBill

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

My extra-sensory perception tells me you are in the Mountain time zone of North America.

UNIX time is Universal (nee GMT, AKA Zulu) time, so you do need to adjust to that.



SELECT DATEADD(hh,-7,DATEADD(ss,1058484229,'19700101'))

Also, one of your clocks is off by 13 minutes...

--Jonathan

rayd8 Old Hand Points: 325 More actions · Answer 4

Thanks Jonathan and SQLBill! Great input and you've all helped a great deal. The data is stored as the INT data type. We didn't consider that UNIX time would be universal.