Date conversions

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

converting human back to seconds :

date -j -f "%Y-%m-%d" 2006-06-30 +"%a %F"

date -j -f “%Y-%m-%dT%H:%M:%SZ” 2016-12-23T14:12:28Z +”%s”

AWK

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 Dtrace will output nano seconds since 1970 in “walltimestamp” function, so just lop off the nanoseconds and you can convert it to date

date_secs=walltimestamp/1000000;

Postgres pl/pgSQL

Date deltas in seconds ( multiply by 1000 to get ms as the values preserves the decimal)

extract(epoch from fn.tm-st.tm)

Oracle

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;

example

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

Oracle TIMESTAMP

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:

http://www.epochconverter.com/

though the didn’t include AWK

  – who uses AWK these days anyway?

NOTE:  highcharts uses epoch for X-axis which makes formatting and manipulation easy, but be aware that highcharts usesmilliseconds instead of seconds.

For example to format the hover tooltip of a point in highchart to hour colon minute. (highchart seems to use the standard date command formatting on UNIX)

 tooltip: {
      formatter: function () {
        return Highcharts.dateFormat("%H:%M ", this.x) ;
      }
    },

highchart date format info: http://stackoverflow.com/questions/7101464/how-to-get-highcharts-dates-in-the-x-axis