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

Arjen van der Meijden acmmailing at tweakers.net
Tue May 24 18:19:50 CEST 2005

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 

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;
!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
!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
!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

More information about the users-list mailing list