Tag Archives: monetdb

Monetdb : how to get schema and tables information ?

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”:

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';

The resultset :

+------+---------------+----------+-------------+
| 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 |
+------+---------------+----------+-------------+ 

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 :

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

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