I recently encountered two conversion issues that are specific to working with computers. I address converting unix epoch date format to human readable date format here. My next post will address converting IP addresses stored as decimal integers to the more familiar dotted format.
There’s a popular work request management software product called Teamtrack from Serena software. The version deployed in my shop stores dates in its database using unix epoch format, which is the number of seconds since Jan 1, 1970. To convert this in a sql server t-sql query, I used:
select dateadd(s, table.date_field, '19700101') as friendlydate from table
Interestingly, there were a few fields where I needed to do a slight modification, and I’m not sure why. Perhaps another timezone?
select dateadd(s, table.date_field, '19691231 19:00:00') as friendlydate from table
Forum poster jciappas from Brazil posts the Oracle PL/SQL version.
And at a dedicated web site for epoch converters, you can find code for doing the conversions in a variety of languages and databases. This web site covers going back and forth from unix epoch to human readable.