SQL to extract matlab date from postgres db

Question

I'd like to construct a query to "convert" a postgresql datetime to a matlab datenum. Experience with other DBs has shown me that converting the date on the DB side is much faster than doing it in matlab.

Matlab stores dates as number of days (including fractions) since an arbitrary epoch of a gregorian, non-existent date of 00-00-0000.

On Oracle, it's simple, because Oracle stores dates internally like matlab does, but with a different epoch.

select (date_column_name - to_date('01-Jan-0001') + 365) ...

A straightforward conversion of this to PG syntax doesn't work:

select (date_column_name - date '01-Jan-0001' + interval 365) ...

I've started with a particular day in matlab, for testing:

>> num2str(datenum('2010-10-02 12:00'))

ans =

734413.5

I've been in and out of the pg docs all day, extracting epochs and seconds, etc. And I've gotten close. Basically this gets the seconds in an interval, which I just divide by the seconds in a day:

Select cast(extract(epoch from (timestamp '2010-10-02 12:00' 
                               - timestamp '0000-01-01 23:10'
                               + interval '2 day'
                               )
                   ) as real
           )/(3600.0*24.0)  AS MDate

answer: 734413.51111111111

But that exhibits some bizarre behavior. Adjusting the minutes from the epoch timestamp doesn't change the answer, except at one particular minute - i.e 23:09 is one answer, 23:10 is another, and it stays the same from 23:10 to 23:59. (other hours have similar behavior, though the particular "minute" is different.)

Any ideas? Maybe on another way to do this?

edit: using 8.4.2

Answer 1

It seems that the cast was the problem.

Select extract(epoch from (timestamp '2010-10-02 12:00:01' 
       - timestamp '0000-01-01 00:00' 
       + interval '1 day'))/(3600.0*24.0)

works like a champ.

Answer 2

Well, extract(epoch from t::timestamp) will give seconds since the UNIX epoch (01 Jan 1970), and produces 1286017200 for '2010-10-02 12:00:00'.

Matlab gives 734413.5 for the same timepoint, but that's in days- so 63453326400 seconds, an offset of 62167309200.

So to convert postgres epoch time to matlab datenum, we should be able to just add that offset and convert back to days.

steve=# select (extract(epoch from '2010-10-02 12:00:00'::timestamp) + 62167309200) / (24*3600);
 ?column? 
----------
 734413.5
(1 row)