Finding Unix Time

[JimKnicely]

Unix time, or POSIX time, is a system for describing instances in time. It’s generally defined as the number of seconds that have elapsed since midnight Coordinated Universal Time (UTC), January 1, 1970.

Unix time is used widely in Unix-like and many other operating systems and file formats. It is neither a linear representation of time nor a true representation of UTC.

We can check the Unix time on the Linux command line via the +%s option of the date command, like this:

Code: Select all

bash-3.2$ date
Fri Jan 18 15:01:18 EST 2013
bash-3.2$ date +%s
1358539281

But how can we check the Unix time in our database queries?

The following examples will show you how in Vertica, MySQL, SQL Server and Oracle:

Vertica:

We can grab the EPOCH of the current time using the EXTRACT function to find the current Unix time in Vertica:

Code: Select all

dbadmin=> SELECT now() "Now", EXTRACT(EPOCH FROM now()) "UNIX Time";
              Now              |     UNIX Time
-------------------------------+-------------------
2013-01-18 14:32:07.826871-05 | 1358537527.826871
(1 row)

MySQL:

MySQL makes it really easy to get the current Unix time with the UNIX_TIMESTAMP function:

Code: Select all

MySQL> SELECT now() "Now", UNIX_TIMESTAMP(now()) "UNIX Time";
+---------------------+------------+
| Now                 | UNIX Time  |
+---------------------+------------+
| 2013-01-18 14:41:15 | 1358538075 |
+---------------------+------------+
1 row in set (0.00 sec)

SQL Server:

There’s no built in function in SQL Server for finding Unix time, but we can do a little date arithmetic do get it:



Oracle:

Oracle doesn’t have a built in function for Unix time either so it’ll have to be calculated manually:

Code: Select all

SQL> SELECT sysdate "Now", (sysdate - to_date( '01/01/1970', 'MM/DD/YYYY' )) * 24 * 60 * 60 "UNIX Time"
  2    FROM dual;

Now        UNIX Time
--------- ----------
18-JAN-13 1358520892

Have fun!