Converting a timestamp string to UNIX timestamp in MonetDB

Question

I'd like to convert a timestamp like "2018-02-14 23:59:59" to a UNIX timestamp. I found that the opposite is possible using select sys.epoch(unix_timestamp);. The functions str_to_date, str_to_time or str_to_timestamp are of no help, even if I specify the format string "%s" as seen here.

In other words, I'm looking for the MonetDB equivalent of the MySQL function UNIX_TIMESTAMP().

Answer 1

Found the solution:

select sys.epoch(str_to_timestamp('2018-02-15 10:04:00', '%Y-%m-%d %H:%M:%S'));

1518689040

Obviously, if you need this in milliseconds, just multiply by 1000 like so:

select sys.epoch(str_to_timestamp('2018-02-15 10:04:00', '%Y-%m-%d %H:%M:%S')) * 1000;

How I found it, in case anyone has a similar problem in the future:

You can view all system functions using:

select * from sys.functions;

A prettier solution can be found here: Is there a way to view MonetDB Function signatures

It turns out the epoch function can take a timestamp as a parameter.