Monetdb-timezone the difference in the output of the epoch function (converting int to datetime format)

Question

Q1: I want to convert the unix timestamp (INT) to the monetdb timestamp (format "YYYY-MM-DD HH: MM: SS") but this gives me 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 the current time of my system, it gives the same difference

I tried to use

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

but he did not change the result

How can I solve this problem?

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

Answer 1

Not knowing anything about MonetDB, but a lot about time zones, I decided to look in their documentation to see what types of data are supported and how conversions are processed.

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

To get the value defined for a specific time zone, they offer the following example:

 SET TIME ZONE INTERVAL '1' HOUR TO MINUTE 

I assume that this means that the database will be shifted all the time by 1 hour, effectively placing the values ​​in UTC+01:00 , for example, this is the offset for British Daylight Saving Time.

The page also indicates problems that can occur using only the offset to set the time values ​​(see TimeZone! = Offset in wiki of the TimeZone theme ). It also offers a list of different named time zones. But it does not show how to set the time zone for one of the named values. In addition, their list appears to be proprietary and incomplete. Although at first glance they seem to be similar to IANA / Olson timezone databases, the identifiers that they specify are not valid TZDB names.

There are some other features listed on this page without much explanation. One that looks promising for your needs is LOCALTIMESTAMP . Perhaps this takes into account the local time zone, which is apparently what you were looking for.

I could not find any additional information specific to the processing of the date / time / time of MonetDB. The documentation looks pretty incomplete. You might want to check out their mailing list.