Hi Fabian,

I have "unix timestamps" in a database and want to operate on them as timestamps, or convert them to be real timestamps. Although I found at least one way to make it work, I don't like it and was looking for alternatives.

On 24-5-2005 15:54, Fabian wrote:

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.

Euh, yeah. The timestamp '...' notation is at least a lot shorter and clearer. What I tried to achieve there is to convert my unix timestamps to SQL timestamps. I.e. take the 'epoch' and add the seconds to it. The "..." actually worked, I hadn't even thought about it being illegal SQL. As a matter of fact, this also works: timestamp "1970-01-01 01:00:00"

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

That indeed works. But not with an integer value or a column identifier:

select timestamp '1970-01-01 01:00:00' + INTERVAL RecordTimestamp SECOND FROM pwclicks; Yields: !syntax error, unexpected IDENT, expecting STRING in: "select timestamp '1970-01-01 01:00:00' + interval recordtimestamp"

The postgresql trick won't work because of type incompatibilities: select timestamp '1970-01-01 01:00:00' + RecordTimestamp * INTERVAL '1' SECOND FROM pwclicks Yields: !types bigint(19,0) (lng) and sec_interval(0,0) (lng) are not equal

And casting the column to a string also won't work. select timestamp '1970-01-01 01:00:00' + INTERVAL CAST(RecordTimestamp AS String) SECOND FROM pwclicks Yields: !syntax error, unexpected CAST, expecting STRING in: "select timestamp '1970-01-01 01:00:00' + interval cast"

I did notice however that timestamp '1970-01-01 01:00:00' + '1' is seen as + interval '1' second. It turns out that this works: SELECT timestamp '1970-01-01 01:00:00' + CAST(RecordTimestamp AS String) FROM pwclicks;

The concatenation of the LL is useless, I just thought it was necessary since it should be converted to long.

But from what I'm able to read from the sql-spec, the first of these three queries should be correct since a column reference is a <value expression primary> and even functions should be allowed in that place as well?

...

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)

I understand that, but since I'm not an SQL-99 expert (I'm spoiled with postgresql's and mysql's additions to sql), I was looking for the easiest way to do that in MonetDB/SQL99.

...

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.

Well, I discovered a shorter notation of a still not very pretty way of converting my bigint-unix-timestamp to SQL timestamps. Thanks for your time and support.

Best regards,

Arjen van der Meijden