[MonetDB-users] Converting a unix timestamp (bigint) to timestamp/date?

Arjen van der Meijden acm at tweakers.net
Tue May 24 11:35:20 CEST 2005

Hi List,

I'm trying to have an easy way to convert some fields that where 
initialy (in MySQL) stored as unsigned integers. I converted them using 
a dump/restore to MonetDB's bigint.

Currently this works:
CAST("1970-01-01 01:00:00" as timestamp)
    + (CAST(RecordTimestamp AS STRING) || 'LL')

Appearantly bigint (long) is incompatible with sec_interval (long)?

But afaik this should be possible in SQL as well:
CAST("1970-01-01 01:00:00" as timestamp)
    + INTERVAL RecordTimestamp SECONDS

And of course in MySQL there is FROM_UNIXTIME(RecordTimestamp), in 
PostgreSQL there is: timestamp 'epoch' + RecordTimestamp * interval '1 

Converting it back is also quite a bit of work:
SELECT ((CAST(current_timestamp as timestamp) - CAST("1970-01-01 
00:00:00" as timestamp)) / 1000);

That seems to work to get the current time's epoch... Why aren't 
timestamptz and timestamp compatible, btw?

In MySQL there is UNIX_TIMESTAMP(RecordTimestamp), in PostgreSQL 
EXTRACT(epoch FROM current_timestamp)::bigint

I was also looking for a way to format the date/timestamp output, is 
that possible? Casting it to a SQL-date is rather easy of course, but 
other formatting will very likely become a big mess if EXTRACT where to 
be used. Since my client-language is php, I either need preformatted 
output or a unix timestamp for ease of use.
In SQL it is often better to work with dates/timestamps, rather than the 
not really correct unix timestamp arithmetics (add 86400 seconds to get 
the next day and so on).

Did I overlook some easy to use extentions to SQL, or do I really need 
to use the constructs I already found?

Best regards,

Arjen van der Meijden

More information about the users-list mailing list