Move to data-driven decision making with my new NPS survey software.

All my readers receive 10% off for life with code: ian

sql server convert unix timestamp to date

December 1, 2005

One of the HelpSpot trial users is trying to do some reporting directly from the database and running into a problem with HelpSpot's date storage. For simplicity HelpSpot stores all dates as UNIX timestamps. This makes it easy to keep things cross platform on the DB front, because it's simply stored as an integer. It's also ideal since PHP basically requires this format to use the date functions.

The issue is that Microsoft SQL Server doesn't understand timestamps in this format so I thought I'd post the solution here in case anyone needs this. It's really simple, all you need to do is use SQL Servers dateadd() function to add the number of seconds to the UNIX timestamp starting point of January 1st, 1970. So simply:

SELECT dateadd(ss, {UNIX_timestamp}, '19700101')

or in a query:

SELECT xRequest, dtGMTOpened, dateadd(ss,dtGMTOpened,'19700101') AS opendate FROM HS_Request

→ Share your thoughts with me on Twitter
Don't Miss My Critical Posts

I won't bother you with short posts or off topic musings. You'll also receive my ebook on enterprise sales for bootstrappers for free.