Search

OakieTags

Who's online

There are currently 0 users and 51 guests online.

Recent comments

Affiliations

Date conversions

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

#555555;">Dates are a surprising pain for correlating data an at the same time being understandable to the human reader.

#555555;">For a generic form we can take seconds since 1970, on UNIX, with

#555555;">$ date '+%s'
1311372938

#555555;">but then how to we convert it back? There is a neat trick for that, “date -d @”

#555555;">$ date -d @1311372938  +%H:%M:%S
22:15:38

#555555;">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 :

#555555;">date_secs=` date '+%s'`
date -d @$date_secs +%H:%M:%S
22:23:34

#555555;">AWK

#555555;">AWK takes a bit of a trick to convert seconds to human format:

#555555;">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)

#555555;">Dtrace
Dtrace will output nano seconds since 1970 in “walltimestamp” function, so just lop off the nanoseconds and you can convert it to date

#555555;">date_secs=walltimestamp/1000000;

#555555;">Oracle

#555555;">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

#555555;">SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual;

#555555;">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.

#555555;">If Oracle is reading data from an external source in seconds since 1970 we can just reverse the process

#555555;">select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') + seconds_since_1970 / 86400 ),'DD-MON-YYYY') from dual;

#555555;">example

#555555;">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

#555555;">Oracle TIMESTAMP

#555555;">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:

#555555;">select cast(begin_interval_time as date ) from DBA_HIST_SNAPSHOT;

#555555;">UPDATE: just found this link which is pretty good at covering a wide range of languages and databases:

#555555;">#2970a6;" href="http://www.epochconverter.com/">http://www.epochconverter.com/

#555555;">though the didn’t include AWK :)  – who uses AWK these days anyway?

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

#555555;">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)

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

#555555;">highchart date format info: #2970a6;" href="http://stackoverflow.com/questions/7101464/how-to-get-highcharts-dates-in-the-x-axis">http://stackoverflow.com/questions/7101464/how-to-get-highcharts-dates-in-the-x-axis