How to convert a unix timestamp (INT) to monetdb timestamp ('YYYY-MM-DD HH:MM:SS') local time format

Question

Q1: I want to convert a unix timestamp (INT) to monetdb timestamp ('YYYY-MM-DD HH:MM:SS') format but it is giving me the GMT time not my actual time.

When I do

select (epoch(cast(current_timestamp as timestamp))-epoch(timestamp '2013-04-25 11:49:00'))

where 2013-04-25 11:49:00 is my systems current time it gives the same difference

I tried using

set time zone interval '05:30' HOUR TO MINUTE;

but it did not change the result

How can I solve this problem??

Example Problem:
I wanted to convert unix timestamp 1366869289 which should be around "2013-04-25 11:25:00" but monetdb gives "2013-04-25 05:55:00"

Answer 1

Knowing nothing about MonetDB, but a lot about timezones, I decided to look in their documentation to see what kind of datatypes are supported and how conversions are handled.

I found this page on Temporal data types. Based on that, I can conclude that a timestamp in MonetDB is always intended to reference UTC/GMT time - which is consistent with other systems.

In order to get a value that is for a particular time zone, they offer the following example:

SET TIME ZONE INTERVAL '1' HOUR TO MINUTE

I assume this means to set the database to offset all times by 1 hour, effectively placing the values all in UTC+01:00, such as is the offset for British Summer Time.

The page also goes on to point out the problems that can arise with using just and offset to adjust time values (see TimeZone != Offset in the TimeZone tag wiki). It also offers a list of various named time zones. But it does not show how to set a time zone to one of the named values. Also, their list appears to be proprietary, and incomplete. While at first glance they appear to have similarities to the IANA/Olson time zone database - the identifiers they specify are not valid TZDB names.

There are some other functions listed on this page, without much explanation. One that looks promising for your needs is LOCALTIMESTAMP. Perhaps this will take the local time zone into account, which appears to be what you were looking for.

I could not find any additional details specific to MonetDB date/time/timezone handling. The documentation appears to be fairly incomplete. You might want to reach out to their mailing list.