Technical Blog Post
Abstract
SQL Server: Convert a Unix epoch time string to a human readable date time field
Body
Searching though a table in a SQL Server database, I found the date field I was looking for. To my dismay, the field's value was a string in Unix epoch time - a value representing the number of seconds since January 1, 1970.
I decided to convert the string to a date/time format that I was more used to seeing. After much wailing and gnashing of teeth, I found the format I needed.
Let's explore this step by step:
The original query consisted of:
SELECT FNAME, LNAME, JOIN_DATE
FROM MEMBERS
WHERE FNAME = 'SUE' AND
LNAME = 'JONES';
This was a nice, relatively pithy SQL query and it yielded:
FNAME LNAME JOIN_DATE
SUE JONES 0001516993312125
I did a quick web search and learned that SQL Server's DATEADD() function was the ticket to what I needed. However, my first attempt failed miserably, as did my second and third attempts. However, these failures were instructive, as they led to the solution.
1. The JOIN_DATE column is a string, so it must be converted to a numeric value.
DATEADD(SS, CONVERT(BIGINT, JOIN_DATE), '19700101')
2. The value '0001516993312125' is too long - we must truncate the leading zeroes and the last 3 digits:
DATEADD(SS, CONVERT(BIGINT, SUBSTRING(JOIN_DATE, 4,10)), '19700101')
3. At last I was getting where I wanted to be:
SELECT FNAME, LNAME, DATEADD(SS, CONVERT(BIGINT, SUBSTRING(JOIN_DATE, 4,10)), '19700101')
FROM MEMBERS
WHERE WHERE FNAME = 'SUE' AND
LNAME = 'JONES';
yielded:
FNAME LNAME 2 N/A
SUE JONES 2018-01-26 19:01:52
4. The time value was now human-readable, but I didn't like the column name, 2/NA. There was also another issue - the time was wrong! This was puzzling until I realized that the result failed to account for the time zone in which I live (GMT -5, if you must know.) I decided to fix the column name and represent the value in local time:
SELECT FNAME, LNAME, , DATEADD(HH, -5, (DATEADD(SS, CONVERT(bigint, SUBSTRING(c2.OIN_DATE, 4,10)), '19700101'))) AS 'Date_Joined',
FROM MEMBERS
WHERE WHERE FNAME = 'SUE' AND
LNAME = 'JONES';
FNAME LNAME DATE_JOINED
SUE JONES 2018-01-26 14:01:52
The query wasn't pretty, and it had been a slog, but I had what I needed. In celebration, I broke out the ginger tea and biscuits.
UID
ibm11120659