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

Fabian Fabian.Groffen at cwi.nl
Tue May 24 15:54:22 CEST 2005

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.


More information about the users-list mailing list