Finding Unix Time

unix-timeUnix 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:

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?

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:

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)

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:

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:

SQL> SELECT sysdate "Now",
. 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!

 

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.