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
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 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)
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