Dates are a surprising pain for correlating data an at the same time being understandable to the human reader.
For a generic form we can take seconds since 1970, on UNIX, with
$ date '+%s' 1311372938
but then how to we convert it back? There is a neat trick for that, “date -d @”
$ date -d @1311372938 +%H:%M:%S 22:15:38
so in scripts we can load up the date into seconds, that can be subtracted to get deltas and at the same time converted easily to any date format :
date_secs=` date '+%s'` date -d @$date_secs +%H:%M:%S 22:23:34
AWK takes a bit of a trick to convert seconds to human format:
date_secs=1311372938 # setup the UNIX command d="date -d @"date_secs" +%H:%M:%S" # run the command to getline and put it into "date_human_format" d | getline date_human_format # be sure and close the open descriptor close(d)
Dtrace will output nano seconds since 1970 in “walltimestamp” function, so just lop off the nanoseconds and you can convert it to date
Oracle has the nice feature of returning the days along with fractional days when subtracting two dates, so it seems like just subtracting off 1970 should work and then multiplying by the seconds in a day
SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual;
But since Oracle can already subtract dates there is little need to do the conversion unless extracting the data to be used by an external consumer.
If Oracle is reading data from an external source in seconds since 1970 we can just reverse the process
select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') + seconds_since_1970 / 86400 ),'DD-MON-YYYY') from dual;
SQL> select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') +1311372938 / 86400 ),'DD-MON-YYYY') from dual; TO_CHAR((TO ----------- 22-JUL-2011 SQL> SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual; (SYSDATE-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400) ------------------------------------------------------ 1311424000
One issue on Oracle though, the variable type timestamp can be a bit frustrating to work with, but it can be cast into a date, for example:
select cast(begin_interval_time as date ) from DBA_HIST_SNAPSHOT;
UPDATE: just found this link which is pretty good at covering a wide range of languages and databases:
though the didn’t include AWK :) – who uses AWK these days anyway?