Walker News

How To Instantly Convert Epoch Time In SQL Output?

Question: A table stores date/time value in UNIX epoch time format, which is not human-readable when seeing them in SQL output. So, is there a way or function to convert these epoch time values on the fly in a SQL SELECT statement?

Answer: It’s possible and it’s “pretty easy”, if it’s on Linux :)
This example is based on Red Hat Enterprise Linux 5 and DB2 9.1. There is no native DB2 function for converting UNIX epoch time.

Instead of build a DB2 UDF (user-define function), am creating a “filter” using awk and GNU date that come with RHEL. Having said that, this filter is OS-dependent but can work with SQL output of any database systems.

1. Create a shell script that named as EPTC (short for “epoch time converter”), which barely contains 3 lines (actually only 2 core lines):
#!/bin/sh

declare -i EPT=`echo $1 | sed -e "s/\.//"`

echo -e "`date -u -d \"1970-01-01 $EPT secs\" +%d-%m-%Y`\\c"

2. Now, pipe the SELECT statement output to awk command that executes EPTC, to convert the epoch time value in SQL output on the fly:

Though without epoch time conversion function in DB2, we still can easily convert the epoch time value in SQL output/

Apparently, it’s not flexible as compare to DB2 function, but this filter is a quick and workable solution. If you copy and use it right away, it takes not more than a minute, I guess.
There is no constraint on the number of columns in SQL output, but EPTC is only capable to convert one column of epoch time and the converted value is always printed on first column in its output.

For example,
echo "DB2 1356134401 6666" | \
awk '{system("sh EPTC "$2); printf ",%s,%d\n",$1,$3}'

is printed as:
22-12-2012,DB2,6666
echo "1356048001 EPOCH" | \
awk '{system("sh EPTC "$1); printf ",%s\n",$2}'

is output as:
21-12-2012,EPOCH

Custom Search

2016  •  Privacy Policy