Convert string to timestamp in MonetDB

Question

How does one convert a string/varchar to a timestamp in MonetDB ?

Like this, but with millisecond precision (to six decimal places, ideally):

sql>select str_to_date('2008-09-19-18.40.09.812000', '%Y-%m-%d-%H.%M.%6S');
+--------------------------+
| str_to_date_single_value |
+==========================+
| 2008-09-19               |
+--------------------------+
1 tuple (0.312ms)

I'm not sure whether str_to_date is built in or whether I created it ages ago and forgot.

create function str_to_date(s string, format string) returns date
external name mtime."str_to_date";

Edit: expected output something like

+---------------------------------+
| str_to_timestamp_single_value   |
+=================================+
| 2008-09-19 18:40:09.812000      |
+---------------------------------+

Answer 1

Monetdb time conversion functions are listed in :

• [Monetdb installation folder]\MonetDB5\lib\monetdb5\createdb\13_date.sql.

Besides the str_to_date function, there is a str_to_timestamp function.

The syntax of the format string follows the MySQL one.

Example :

select sys.str_to_timestamp('2016-02-04 15:30:29', '%Y-%m-%d %H:%M:%S');

Answer 2

The date/time specifiers might need to be changed:

select str_to_date('2008-09-19-18.40.09.812000','%Y-%m-%d-%H.%i.%s.%f')

output:

2008-09-19 18:40:09.812000

_{*monetdb could be different, although in standard SQL these are the standard date specifiers.}

You could also use date_format in addition to str_to_date:

select date_format(str_to_date('SEP 19 2008 06:40:09:812PM','%M %D %Y %h:%i:%s:%f%p'),'%Y-%m-%d-%H.%i.%f');

output:

2008-09-19-18.40.812000