As other SQL database engines, monetdb allows to query schema and table information through SQL queries over a few system tables, namely : sys.schemas, sys.tables, and sys.columns.

The following query will give columns details for the system table « tables »:
[code lang=sql]
SELECT c.id, c.name, c.type, c.type_digits
FROM sys.columns c LEFT JOIN sys.tables t ON c.table_id = t.id
WHERE t.name = ‘tables’;
[/code]

The resultset :
[code]
+——+—————+———-+————-+
| id | name | type | type_digits |
+======+===============+==========+=============+
| 5211 | id | int | 32 |
| 5212 | name | varchar | 1024 |
| 5213 | schema_id | int | 32 |
| 5214 | query | varchar | 2048 |
| 5215 | type | smallint | 16 |
| 5216 | system | boolean | 1 |
| 5217 | commit_action | smallint | 16 |
| 5218 | access | smallint | 16 |
| 5219 | temporary | smallint | 16 |
+——+—————+———-+————-+
[/code]

Nicolas Riousset

Président et fondateur de NeoLegal, développe des solutions logicielles qui facilitent le quotidien des professionnels du droit des sociétés.

nicolas.riousset.com

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 »;
[/code]

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’);
[/code]

Nicolas Riousset

Président et fondateur de NeoLegal, développe des solutions logicielles qui facilitent le quotidien des professionnels du droit des sociétés.

nicolas.riousset.com