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 seconds'
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