Finding Unix Time
April 17, 2013 
0 Comments
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:
Fri Jan 18 15:01:18 EST 2013
bash-3.2$ date +%s
1358539281
In Vertica to find the Unix time we can grab the EPOCH of the current time using the EXTRACT function to find the current Unix time in Vertica:
Now | UNIX Time
-------------------------------+-------------------
2013-01-18 14:32:07.826871-05 | 1358537527.826871
(1 row)
For giggles, here’s how to find Unix Time in MySQL, SQL Server and Oracle:
MySQL:
MySQL makes it really easy to get the current Unix time with the UNIX_TIMESTAMP function:
+---------------------+------------+
| 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:
. 2 (sysdate - to_date( '01/01/1970', 'MM/DD/YYYY' )) * 24 *
. 3 60 * 60 "UNIX Time"
. 4 FROM dual;
Now UNIX Time
--------- ----------
18-JAN-13 1358520892
Have fun!
No comments yet... Be the first to leave a reply!