Monthly Archives: May 2016

Monetdb : how to convert a string to a timestamp ?

Monetdb is great tool, with a not so great documentation… Converting a string/varchar to a timestamp is easy once you know the proper function to call.

The time conversion functions are listed in the [Monetdb installation folder]\MonetDB5\lib\monetdb5\createdb\13_date.sql file :
[code lang=sql]
create function str_to_date(s string, format string) returns date
external name mtime.”str_to_date”;

create function date_to_str(d date, format string) returns string
external name mtime.”date_to_str”;

create function str_to_time(s string, format string) returns time
external name mtime.”str_to_time”;

create function time_to_str(d time, format string) returns string
external name mtime.”time_to_str”;

create function str_to_timestamp(s string, format string) returns timestamp
external name mtime.”str_to_timestamp”;

create function timestamp_to_str(d timestamp, format string) returns string
external name mtime.”timestamp_to_str”;

The syntax of the format string follows the MySQL one.

And eventually, here is a conversion example of a varchar to a timestamp
[code lang=sql]
select str_to_timestamp(‘2016-02-04 15:30:29’, ‘%Y-%m-%d %H:%M:%S’);