Hi Arjen, I will try to give you some answers, but I don't fully understand what you want exactly. Thanks for giving MonetDB/SQL a go in any case. Arjen van der Meijden wrote:
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.
unsigned int -> bigint sounds good to me
Currently this works: CAST("1970-01-01 01:00:00" as timestamp) + (CAST(RecordTimestamp AS STRING) || 'LL')
if CAST("1970-01-01 01:00:00" as timestamp) then I believe you spotted a bug, since it should be CAST('1970-01-01 01:00:00' as timestamp) (unless you have a column named like that). However, I think the SQL standard way of specifying a timestamp would be: timestamp '1970-01-01 01:00:00'. It's not completely clear to me what you want to do here: you cast a varchar to a timestamp then to add a string to that timestamp. I can imagine that this goes wrong or does not completely what you expect it to do. I think it would be a feature request to add something like EXTRACT(epoch FROM timestamp '1970-01-01 01:00:00') or similar to get a UNIX epoch that PHP works well with.
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 it does: monetdb-> select timestamp '2005-05-24 13:00:00' + interval '5' second; +----------------------+ | sql_add_single_value | +======================+ | 2005-05-24 13:00:05.0 | +----------------------+ 1 row
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?
I don't know for sure. The timestamp with time zone type was only recently introduced, perhaps some relaxing should be applied here.
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.
Output of MonetDB/SQL is according to the SQL/99 standard. Again it would be a feature request to have some epoch extraction functions (which I consider to be highly useful for use with PHP)
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).
MonetDB/SQL indeed implements the Gregorian calendar which helps you ;)
Did I overlook some easy to use extentions to SQL, or do I really need to use the constructs I already found?
Did this help you a bit perhaps? I'd be happy to help you any further by means of support or getting things working/implemented. Regards, Fabian