MonetDB

009. JSON and UUID Data Type in MonetDB

JSON Data Type

We have some JSON data. We can create a column that would accept string data type and put this JSON inside. If we want more functionality, we can use special JSON data type. In that case only valid JSON could be placed into that column.

We create a JSON column like this. Data type could be JSON, or JSON(100) if we want to limit length.

CREATE TABLE JsonTable (JsonColumn JSON );

We will place our JSON sample in that table.

INSERT INTO JsonTable ( JsonColumn ) VALUES ( 
'{ "store": { "book": [ { "Title": "Door"   ,  "price": 8.95   }
                      , { "Title": "Sword"  ,  "price": 12.99  } ]
            , "bicycle":  { "color": "red", "price": 19.95  }    } 
 , "employee": { "Name": "Runako"  }         }'                    )

We can read from JSON column like from any column with "SELECT * FROM JsonTable;", but if we want to get only some part of JSON code, we have to use JSONpath expressions. JSONpath expressions can define what part of JSON code we want to read. JSONpath expression should be placed as argument in JSON.FILTER function. Result will always be returned inside square brackets. Result is also of JSON data type. JSONpath expressions are case sensitive.

SELECT JSON.FILTER( JsonColumn, '$') FROM JsonTable;
[--this will return the whole json string--]
$ represents root of a JSON.
SELECT JSON.FILTER( JsonColumn, '$.store.bicycle.color') FROM JsonTable;
["red"]
We read the direct child of the hierarchical path.
SELECT JSON.FILTER( JsonColumn, '$..Title') FROM JsonTable;
["Door","Sword"]
SELECT JSON.FILTER( JsonColumn, '$.store..color') FROM JsonTable;
["red"]
This will return all elements that are of type "title" regardless
of where they are in the hierarchy. We can limit this type
of filter to the interior of the "store" only.
SELECT JSON.FILTER( JsonColumn, 0 ) FROM JsonTable;
[{ "book": [ { "Title": "Door"   ,  "price": 8.95   }
       , { "Title": "Sword"  ,  "price": 12.99  } ]
 , "bicycle":  { "color": "red", "price": 19.95  }    }]
   
This would return the first element of the JSON.
It will return a value inside the "store".
This time we don't use JSONpath, but a number.
SELECT JSON.FILTER( JsonColumn, '$.store.book.[0]') FROM JsonTable;
[{ "Title": "Door", "price": 8.95  }]
We will get the first element in the array.
SELECT JSON.FILTER( JsonColumn, '$.store.book[0,1]') FROM JsonTable;
[{ "book": [ { "Title": "Door"   ,  "price": 8.95  }     
       , { "Title": "Sword"  ,  "price": 12.99  } ] 
 , "bicycle":  { "color": "red", "price": 19.95  }    }]
We can read the first two elements in the array.
Notice that we don't have a period after "book".
SELECT JSON.FILTER( JsonColumn, '$.store.book.[0].Title') FROM JsonTable;
["Door"]
We can read the "Title" member from the first element in the array.
SELECT JSON.FILTER( JsonColumn, '$.store.book.[0].*') FROM JsonTable;
["Door",8.95]
We will only get the values from the first element in the array.
SELECT JSON.FILTER( JsonColumn, '$.store.book.[*]') FROM JsonTable;
[{ "Title": "Door" , "price": 8.95 },{ "Title": "Sword" , "price": 12.99 }]
We will read all of the elements in the array.

There are other functions for use with JSON, that do not use JSONpath.

SELECT JSON.TEXT(JsonColumn, '_|_' )  FROM JsonTable;
Door_|_8.95_|_Sword_|_12.99_|_red_|_19.95
This will collect all the values from our JSON. The values will be concatenated with a separator string between them. If we don't provide second argument, the separator will be a space character.
SELECT JSON.NUMBER( JSON '{"n":4}');         4
SELECT JSON.NUMBER( JSON '[4]' );            4
SELECT JSON.NUMBER( '4' );                   4
All of these functions will return the number four as DOUBLE. There is also a function that accepts the same arguments but will return an INTEGER, JSON."INTEGER"(). Both functions will return NULL if they fail.
SELECT JSON.ISOBJECT( JSON '[4]' );         false
SELECT JSON.ISOBJECT( JSON '{"n":4}' );     true
This function will return "true" if it is a JSON object. If it is a JSON array, then it will return false. This function also accepts STRING data type. SELECT JSON.ISOBJECT( '{"n":4}');
SELECT JSON.ISARRAY( JSON '[4]' );          trueThis function returns true when JSON is an array (it also accepts a STRING data type).
SELECT JSON.ISVALID( JsonColumn ) FROM JsonTable;             trueThis function will return "true" if the given JSON code is valid.
SELECT JSON.LENGTH( JsonColumn ) FROM JsonTable;            2This will count the top elements in our JSON.
SELECT JSON.KEYARRAY( JsonColumn ) FROM JsonTable;      ["store","employee"]This function will return all the top keys from the JSON sample. The top-level JSON must be an object, otherwise an error will be returned.
SELECT JSON.VALUEARRAY( JsonColumn ) FROM JsonTable; 
[{ "book": [ { "Title": "Door"   ,  "price": 8.95   }       
                 , { "Title": "Sword"  ,  "price": 12.99  } ] 
 , "bicycle":  { "color": "red", "price": 19.95  }    }
,{ "Name": "Runako"  }] 
 
This function will return the values for the top keys

There is another JSON function that can create JSON code from DOUBLE or CLOB columns. The returned value will be of data type CLOB.

NumberColStringCol
1One
2Two
3Three
CREATE TABLE NumberString ( NumberCol DOUBLE, StringCol CLOB ) 

INSERT INTO NumberString ( NumberCol, StringCol ) VALUES ( 1, 'one' ), ( 2, 'two' ), ( 3, 'three' )

In both cases the functions work in the same way. They concatenate all the values into a JSON string.

SELECT JSON.TOJSONARRAY( NumberCol ) NumberClob, JSON.TOJSONARRAY( StringCol ) StringClob FROM NumberString;
         NumberClob                               StringClob   
[ 1.000000, 2.000000, 3.000000 ]                [ "one", "two", "three" ]

UUID Data Type

A UUID is a unique value created by an algorithm. The UUID looks like this "65950c76-a2f6-4543-660a-b849cf5f2453". The UUID is represented as 32 hexadecimal digits separated into 5 groups of digits. The first group has 8 digits, then we have three groups of 4 digits, and finally there is one group of 12 digits. Together they define a really big number. Every time we call a function to create a new UUID, it will likely create a new value that was never used, anywhere in the world. Such values are used as a synthetic key that is globally unique.

We can create a new UUID value with this function:

SELECT  SYS.UUID() AS UUID;              65950c76-a2f6-4543-660a-b849cf5f2453

If we want to check whether some value is a valid UUID we can use function SYS.ISAUUID( string ). This function will return TRUE if we have a string formatted like 65950c76a2f64543660ab849cf5f2453 or like 65950c76a2f64543660ab849cf5f2453, without hyphens.

SELECT SYS.ISAUUID('e31960fb-dc8b-452d-ab30-b342723e7565');            true

We can transform a string into a UUID data type using the CAST or CONVERT functions, or we can use the UUID unary operator:

select cast( '26d7a80b-7538-4682-a49a-9d0f9676b765' as uuid) as uuid_val;
select convert('83886744-d558-4e41-a361-a40b2765455b', uuid) as uuid_val;
select uuid'AC6E4E8C-81B5-41B5-82DE-9C837C23B40A' as uuid_val;

008. MonetDB – Serial Data Type and Sequences

Serial Data Type

In a table, synthetic (surrogate) key is a column which have a unique value for each row, but its content is not derived from application data, but is generated. If none of the rows is deleted from this table, then this column will usually have numbers as a sequence 1,2,3,4…

Synthetic keyID numberName
1E02387Netsai
2E04105Amos
3E02572Taurai
4E02832Mischeck

There are many advantages in using surrogate keys but this time we will talk only about that how to create them in MonetDB. We can use code like this:

CREATE TABLE SerialTable ( SerialCol SERIAL, Name VARCHAR(10)  )

When we enter data in this column, we do not specified values for SerialCol. Those values will be automatically created because this column is of SERIAL data type.

INSERT INTO SerialTable (Name) VALUES ('Obert')

When we read from this table, this would be result. Number one will be automatically added to SerialCol.

SerialColName
1Obert

If we repeat this INSERT statement several time, each time we would get next number.

SerialColName
1Obert
2Obert
3Obert

This means that, for this column, there is a database object that is remembering last used number, so that next time it can provide next number in a sequence. We can find that database object in sys.sequences system table. Although this table claims that maxvalue is some huge number ( upper limit for BIGINT, or 9223372036854775807  ), this column will actually be of the type INTEGER. Starting value is one, and increment is also one. That is why we are getting numbers 1,2,3…

It is also possible to use BIGSERIAL instead of the SERIAL. In this case our column would be really of the type BIGINT.

If we want to define what kind of integer to use then we are using syntax like this. Instead of INT we can use any other integer data type like TINYINT, SMALLINT, BIGINT, HUGEINT.

CREATE TABLE AutoIncrementTable ( AutoNumberCol INT AUTO_INCREMENT, Name VARCHAR(10) )

In this case sys.sequences table will still show that maxvalue is upper limit for BIGINT, but the real limit will be for INTEGER data type. If we try to enter a value for AutoNumberCol by ourselves, that activity will fail if the number is bigger than 2.7 billion which is upper limit for INTEGER data type.

Maxvalue is always set to BIGINT upper limit because internally MonetDB is storing serial numbers as BIGINT. This doesn't mean that you can not use HUGEINT as AUTO_INCREMENT, you can.

We can generate a new value for our sequence with this statement. "seq_7697" is the name of our sequence. This will increment our sequence by one, so this statement should be used to get next number and then to use it for something. This is usually useful when we want to create new value, and then to use it both in primary key column and foreign key column.

SELECT NEXT VALUE FOR seq_7697

When we are using SERIAL or BIGSERIAL data type, that column will get PRIMARY KEY and NOT NULL constraints.

Always and By Default

Whenever we use SERIAL, BIGSERIAL, we still have ability to enter sequence values by ourselves. Sequence will not be aware of the number we have entered (1) by hand. If sequence try to use the same number (2) that we already entered by hand, it will raise primary key exception.

This is not true if we use AUTO_INCREMENT syntax. In that case we don't have primary key constraint, so it will not be a problem if we enter two same values in our AUTO_INCREMENT column.

If we want to disable ability to enter our own value in sequence column, then we could use this generic syntax.
THIS SYNTAX WILL NOT WORK IN MONETDB. It will create a new sequence, but it will not prevent us from entering our own values.

CREATE TABLE AutoGeneratedTable ( AutoGeneratedCol INT GENERATED ALWAYS AS IDENTITY, Name VARCHAR(10) )

This web page explains that this should not be allowed, but in MonetDB it doesn't work as it should.

[Err] ERROR:  cannot insert into column "rank_id"
DETAIL:  Column "rank_id" is an identity column defined as GENERATED ALWAYS.
https://www.sqltutorial.org/sql-identity/ 

Sequence parameters

Although previous syntax is not prohibiting from entering our own values, that syntax will allows us to use sequence parameters.

CREATE TABLE AutoGeneratedTable ( AutoGeneratedCol INT GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 2 MINVALUE 7 MAXVALUE 10 CACHE 8 CYCLE ), Name VARCHAR(10) )

The parameters (1), allow us to control what will be the content of sys.sequences table (2). As we can see, now the first sequence number will be 7 and next sequence number will be 9 (3).

We can use these parameters:

  • START WITH number– This is the starting number in sequence. It could be negative or positive. If this parameter is not declared, then the default value is MINVALUE for ascending sequences and MAXVALUE for descending sequences. START WITH parameter must be bigger than MINVALUE parameter.
  • INCREMENT BY number – This way we define difference between two consecutive numbers in a sequence. Default value is 1. If "number" is positive then we have ascending sequence. For negative "number", sequence will be descending.
  • NO MINVALUE – NO MIN VALUE means that we are using default value. Default value for ascending sequences is either 1 or START WITH parameter. Default value for descending sequences is minimum value for data type that is associated with the sequence.
  • MINVALUE number – Can be negative or positive. Must be smaller than MAXVALUE.
  • NO MAXVALUE – This means that we are using the default value. For ascending sequences that value will be the maximum value of the data type that is associated with the sequence. For descending sequences, it is either -1 or START WITH number.
  • MAXVALUE number – Can be positive or negative number. Must be greater than minimum value.
  • NO CACHE – System will cache next number to be used. This could create problem because in the case of the system failure, that number will be lost. We can prevent such scenario with NO CACHE.
  • CACHE number –In order to increase performance, we can cache several next values for our sequence. Number of cached values will be smaller or equal to the CACHE number. In the case of the system failure, all these values will be lost.
  • CYCLE – After ascending sequence reach the end of a range, it will start again from the minimal value for associated data type or from the MINVALUE parameter. After descending sequence reach the start of a range, it will start again from the maximal value for associated data type or from the MAXVALUE parameter.
  • NO CYCLE – This will not allow looping. If we reach the value outside of the limits of our range, an error will be raised.

Sequence as an Object

We can create sequence as an object and then we can use that object when we create a new table. We create a new sequence like this. We can define all of the parameters in this statement.

CREATE SEQUENCE "seq_NewSequence" AS SMALLINT START WITH 10 INCREMENT BY 2 MINVALUE 7 MAXVALUE 20 CACHE 8 CYCLE

Now that we have our sequence, we can use it for our new table. We will bind sequence object to our column as a default value.

CREATE TABLE SequenceObject ( SequenceCol SMALLINT DEFAULT NEXT VALUE FOR "seq_NewSequence", name VARCHAR(10) );

If we want to alter our sequence object, then we use this statement. Again, we can use all of the parameters. The only difference is that here instead of "START WITH" we use "RESTART WITH".

ALTER SEQUENCE "seq_NewSequence" RESTART WITH 21 INCREMENT BY 3 MINVALUE 15 MAXVALUE 100 CACHE 10 NO CYCLE

Sequence object can be deleted with this statement. It is only possible to delete sequence object if references to that object are deleted. That means that we have to change DEFAULT value of a columns that are using that sequence object.

DROP SEQUENCE "seq_NewSequence";

Info functions

Two statements below are the same:

SELECT NEXT VALUE FOR seq_NewSequence
SELECT NEXT_VALUE_FOR( 'schema', 'seq_NewSequence' )

Both of them will return number 21, but the next time we call them they will return number 22. They shift the sequence.

If we just want to check what is the next number in a sequence then we can use this function. As we can see, it will always return the same number, sequence will not be shifted after each usage.

SELECT GET_VALUE_FOR('schema', 'seq_NewSequence')

007. Working With Temporal Data Types in MonetDB

Intervals

INTERVAL data types allow us to conduct arithmetic calculations with temporal data types. Keyword DATE is casting operator. This operator is used so that Monetdb transform string to date data type.

SELECT DATE '2021-12-31' + INTERVAL '10' DAY;

It is possible to subtract two dates or times. Result will be interval in seconds.

SELECT date '2020-09-28'
     - date '2020-09-27' AS "DifferenceInSeconds";
SELECT time '14:35:45'
     - time '14:35:40' AS "DifferenceInSeconds";

Current Date and Time

With these variables we can get information about current date, time and time zone (TZ).

SELECT CURRENT_DATE as "Date"
     , CURRENT_TIME as "Time(6)+TZ"
     , CURRENT_TIMESTAMP as "Timestamp(6)+TZ"
     , NOW as "Timestamp(6)+TZ"
     , LOCALTIME as "Time(6)"
     , LOCALTIMESTAMP as "Timestamp(6)"
     , CURRENT_TIMEZONE as "Interval second";

Transform Strings to Temporal Data Types

There are several ways how we can create temporal data types from the strings. If we have dates, times, or timestamps in ISO format then we can use casting operator, casting function or convert function.

SELECT DATE '1987-09-23' AS "Date"
     , TIME '11:40' AS "Time"
     , TIMESTAMP '1987-09-23 11:40' AS "Timestamp";

—————————————————–
You can also use TIMETZ, TIMESTAMPTZ.
SELECT CAST('1987-09-23' as date) AS "Date"
     , CAST('11:40' as time) AS "Time"
     , CAST('1987-09-23 11:40' as timestamp) AS "Timestamp";
SELECT CONVERT('1987-09-23', DATE) AS "Date"
     , CONVERT('11:40', TIME) AS "Time"
     , CONVERT('1987-09-23 11:40', TIMESTAMP) AS "Timestamp";

If string is not in temporal ISO format, then we have  to use functions str_to_date, str_to_time or str_to_timestamp. These functions have second argument where we describe how to interpret string.

SELECT STR_TO_DATE('23-09-1987', '%d-%m-%Y') AS "Date"
     , STR_TO_TIME('11:40', '%H:%M') AS "Time"
     , STR_TO_TIMESTAMP('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "Timestamp";

All the specifiers used to describe dates and times can be found on the bottom of this page:
https://www.monetdb.org/documentation-Dec2023/user-guide/sql-functions/date-time-functions/

Here are just the major specifiers:

%SSeconds (00-59).%uDay of week (1-7).%RTime "15:36".
%MMinutes (00-59).%dDay of month (01-31).%TTime "15:36:40".
%HHours (00-23).%jDay of year (001-366).
%mMonths (01-12).%FISO 8601 format "2023-05-13".
%YYears "1981".%VWeek of year (01-53).%sSeconds from 01.01.1970.
%z"±hh:mm" timezone.%ZName of timezone.

Do it Reverse, Transform Dates and Times to Strings

Counterpart to functions above, are functions that transform temporal types to strings. Here we have functions DATE_TO_STR, TIME_TO_STR and TIMESTAMP_TO_STR. Again, we are using specifiers to describe how we want our string to look like.

SELECT DATE_TO_STR( DATE '1987-09-23', '%Y-%m-%d') AS "Date string"
     , TIME_TO_STR( TIME '11:40', '%H:%M') AS "Time string"
     , TIMESTAMP_TO_STR( TIMESTAMP '1987-09-23 11:40', '%Y-%m-%d %H:%M') AS "Timestamp string";

Extracting Parts of Times and Dates

It is possible to extract parts of temporal data types by using function EXTRACT.

EXTRACT(YEAR FROM CURRENT_DATE)

We can extract these parts:

YEARQUARTERMONTHWEEKDAYHOURMINUTESECONDDOYDOWCENTURYDECADE

It is also possible to use specialized functions to extract temporal parts. Some of these functions have double quotes around them.

"second"(tm_or_ts)"second"(timetz '15:35:02.002345+01:00')2,002345
"minute"(tm_or_ts)"minute"(timetz '15:35:02.002345+01:00')35
"hour"(tm_or_ts)"hour"(timetz '15:35:02.002345+01:00')16
week(dt_or_ts)week(date '2020-03-22')12 >ISO week<
"month"(dt_or_ts)"month"(date '2020-07-22')7
quarter(dt_or_ts)quarter(date '2020-07-22')3
"year"(dt_or_ts)"year"(date '2020-03-22')quarter(date '2020-07-22')

dayofmonth(dt_or_ts)dayofmonth(date '2020-03-22')22
dayofweek(dt_or_ts)dayofweek(date '2020-03-22')7 >Sunday<
dayofyear(dt_or_ts)dayofyear(date '2020-03-22')82

century(dt)century(date '2020-03-22')21
decade(dt_or_ts)decade(date '2027-03-22')202

This two functions can help to extract days or seconds from seconds interval.

"day"(sec_interval)"day"(interval '3.23' second * (24 * 60 * 60))3
"second"(sec_interval)"second"(interval '24' second)24

Max and Min Date or Time

This is how we can find latest or earliest date or time.

greatest(x, y)greatest(date '2020-03-22', date '2020-03-25')date '2020-03-25'
least(x, y)least(time '15:15:15', time '16:16:16')time '15:15:15'

Difference Between Two Timestamps – timestampdiff functions

When subtracting two timestamps we can decide in which measure units to get the result.

timestampdiff(ts_tstz, ts_tstz)select timestampdiff(timestamp '2021-12-31 18:40:40', timestamp '2021-12-30 16:30:20')interval '94220' second
timestampdiff_min(ts_dt_tz, ts_dt_tz)select timestampdiff_min(timestamp '2021-12-31 18:40:40', timestamp '2021-12-31 16:30:20')730
timestampdiff_sec(ts_dt_tz, ts_dt_tz)select timestampdiff_sec(timestamp '2021-12-31 18:40:40', timestamp '2021-12-31 16:30:20')7820
timestampdiff_hour(ts_dt_tz, ts_dt_tz)select timestampdiff_hour(timestamp '2021-12-31 18:40:40', timestamp '2021-12-20 16:30:20')266
timestampdiff_day(ts_dt_tz, ts_dt_tz)select timestampdiff_day(timestamp '2021-12-31 18:40:40', timestamp '2021-12-20 16:30:20')11
timestampdiff_week(ts_tm_tz, ts_tm_tz)select timestampdiff_week(timestamp '2021-12-31 18:40:40', timestamp '2021-02-20 16:30:20')44
timestampdiff_month(ts_tm_tz, ts_tm_tz)select timestampdiff_month(timestamp '2021-12-31 18:40:40', timestamp '2021-02-20 16:30:20')10
timestampdiff_quarter(ts_tm_tz, ts_tm_tz)select timestampdiff_quarter(timestamp '2021-12-31 18:40:40', timestamp '2021-02-20 16:30:20')3
timestampdiff_year(ts_tm_tz, ts_tm_tz)select timestampdiff_year(timestamp '2021-12-31 18:40:40', timestamp '2024-02-20 16:30:20')-3

Truncating Timestamp

This is great function that allows to round our Timestamp to 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'milliseconds' or 'microseconds'. In example bellow we rounded our timestamp to months. That is why all the values to the right side of the month are getting their minimal values.

sys.date_trunc(field_str, timestamp)sys.date_trunc('month', timestamp '2020-03-22 13:16:57.734639');timestamp '2020-03-01 00:00:00.000000'

Unix Time

If we want to transform temporal data in and from Unix time, then we can use these two functions. One will transform number of seconds (counted from 01/01/1970, which is beginning of time) to regular timestamp. Second one will do the reverse. It will transform regular timestamp to seconds from the beginning of Unix time.

sys.epoch(decimal(18,3) seconds)sys.epoch(1234567890.456)timestamptz '2009-02-14 01:31:30.456000+02:00'
epoch_ms(dt_or_tm_or_ts_or_interval)epoch_ms(timestamp '2009-02-13 23:31:30.0')1234567890

006. MonetDB – Data Types

Strings

All CHARACTER data types are using UTF-8.

NameAliasesDescription
CHARACTERCHARACTER(1), CHAR, CHAR(1)0 or 1 character
CHARACTER (length)CHAR(length)Fixed length. String is returned without padding spaces, but it is stored with padding spaces.
CHARACTER VARYING
(length)
VARCHAR (length)"Length" is a maximal number of characters for this string.
CHARACTER LARGE OBJECTCLOB, TEXT, STRINGString of unbounded length.
CHARACTER LARGE OBJECT (length)CLOB (length), TEXT
(length), STRING (length)
String with maximal number of characters.
CLOB(N) is similar to VARCHAR(N), but it can hold much bigger string, although it seems that in MonetDB there is no difference between them.

Binary objects

NameAliasesDescription
BINARY LARGE OBJECTBLOBBinary objects with unbounded length.
BINARY LARGE OBJECT ( length )BLOB ( length )Binary objects with maximal length.

Numbers

Boolean data type can be considered as 0 or 1. So, all data types below are for numbers. "Prec(ision)" is total number of figures. "Scale" are figures used for decimals. For number 385,26; "Prec" is 5 (3+2), and "Scale" is 2. For all number data types, precision is smaller than 18 (or 38 on linux).

NameAliasesDescription
BOOLEANBOOLTrue of False.
TINYINTInteger between -127 and 127 (8 bit)
SMALLINTInteger between -32767 and 32767 (16 bit)
INTEGERINT, MEDIUMINTInteger between -2.147.483.647 and 2.147.483.647 (32 bit)
BIGINT64 bit signed integer
HUGEINT128 bit signed integer
DECIMALDEC ( Prec ), NUMERIC ( Prec )Decimal number, where "Prec" is 18, and "Scale" is 3.
DECIMAL ( Prec )DEC ( Prec ), NUMERIC ( Prec )Zero decimals, but we decide on total number of figures ("Prec").
DECIMAL ( Prec , Scale )DEC ( Prec , Scale ), NUMERIC ( Prec , Scale )We decide on "Prec(ision)" and "Scale".
REALFLOAT(24)32 bit approximate number.
DOUBLE PRECISIONDOUBLE, FLOAT, FLOAT(53)64 bit approximate number.
FLOAT ( Prec )FLOAT(24) is same as REAL, FLOAT(53) is same as DOUBLE PRECISION.
In this case precision can be only between 1 and 53 because this
is special kind of precision ( binary (radix 2) precision ).

Time

These are time data types. "Prec(ision)" now has different meaning. "Prec" is number of figures used for fraction of a second. For 33,7521 seconds, "Prec" is 4. In all cases below, "Prec" has to be between 0 and 6.

NameAliasesDescription
DATEDate YYYY-MM-DD.  
TIMETIME(0)Time of day HH:MI:SS.
TIME ( Prec )TIME with fraction of a second (HH:MI:SS.ssssss).
TIME WITH TIME ZONETIME(0) WITH TIME ZONETIME of day with a timezone (HH:MI:SS+HH:MI).
TIME ( Prec ) WITH TIME ZONESame as above, but now with fraction of a second (HH:MI:SS.ssssss+HH:MI).
TIMESTAMPTIMESTAMP(6)Combination of a DATE and TIME(6) (YYYY-MM-DD HH:MI:SS.ssssss).
TIMESTAMP ( Prec )Same as above, but we decide on "Prec(ision)".
TIMESTAMP WITH TIME ZONETIMESTAMP(6) WITH TIMEZ ONETIMESTAMP(6) with a timezone (YYYY-MM-DD HH:MI:SS.ssssss+HH:MI).
TIMESTAMP ( Prec ) WITH TIME ZONESame as above, but we decide on "Prec(ision)".

INTERVAL

Interval is the difference between two dates and times. There are two measure units to express interval. One is to use number of months. The other is time interval that is expressed in seconds with milliseconds precision. These two types can not be mixed because months have varying numbers of days.

There are three data types if you are using number of months: YEAR, MONTH and YEAR TO MONTH.

SELECT INTERVAL '3' YEAR AS "ThreeYears"
     , INTERVAL '36' MONTH AS "ThirtySixMonths"
     , INTERVAL '0003-01-01' YEAR TO MONTH AS "ThreeYearsAndOneMonth";

If you are using seconds as measurement unit then we have 10 data types:

INTERVAL DAYINTERVAL DAY TO HOURINTERVAL DAY TO MINUTEINTERVAL DAY TO SECONDINTERVAL HOUR
INTERVAL HOUR TO MINUTEINTERVAL HOUR TO SECONDINTERVAL MINUTEINTERVAL MINUTE TO SECONDINTERVAL SECOND
SELECT INTERVAL '1' DAY AS "Day"                                                   --1*24*60*60
            , INTERVAL '1 01' DAY TO HOUR AS "DayToHour"                           --DAY+60*60
            , INTERVAL '1 01:01' DAY TO MINUTE AS "DayToMinute"                    --DAY+60*60+60
            , INTERVAL '1 01:01:01.333' DAY TO SECOND AS "DayToSecond"             --DAY+60*60+60+1,333
            , INTERVAL '1' HOUR AS "Hour"                                          --60*60
            , INTERVAL '01:01' HOUR TO MINUTE AS "HourToMinute"                    --HOUR+60
            , INTERVAL '01:01:01.333' HOUR TO SECOND AS "HourToSecond"             --HOUR+60+1,333
            , INTERVAL '1' MINUTE AS "Minute"                                      --60
            , INTERVAL '01:01.333' MINUTE TO SECOND AS "MinuteToSecond"            --60+1,333
            , INTERVAL '15.333' SECOND AS "Second"                                 --15,333
            ;

For seconds data type, maximal precision is up to milliseconds. Result is always expressed with three decimals.

For "YEAR TO MONTH" we can also write "SELECT INTERVAL '2-5' YEAR TO MONTH".

TIME ZONES

Timestamp is combination of date and time. Timestamp time is time without daylight savings time (DST) regime. This time should represent Greenwich time.

For getting correct time, we should provide time zone with each database connection so that Greenwich time is transformed to local time. Timestamps '15:16:55+02:00' and '14:16:55+01:00' are presenting the same time but for users in different time zones. Timestamp '15:16:55+02:00' and '14:16:55+01:00' are both presenting Greenwich time of '13:16:55+00:00' because 15 – 2=13 and 14 – 1= 13.

If we want, we can change our connection time zone setting by issuing statement "SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE".
This statement "SELECT CURRENT_TIMEZONE" would tell us what is our current time zone.

005. MonetDB – Identifiers and Constants

Comments

There are two types of comments. One-line comments start with "- -" two dashes and they end at the end of the line. First, we will start the server we previously created with "monetdbd start /home/fffovde/DBfarm1". We will also start our mclient application with "mclient -u voc -d voc". Then we can try such comment as:

sql>SELECT * 
more>FROM --some comment
more>total limit 5; 

We can also use multiple lines comment. They start with "/*", and they end with "*/".

sql>SELECT /*
more>some comment
more>here */ * FROM total LIMIT 5;

Identifiers and Keywords

Identifiers and Keywords are not case sensitive.  

If we have an identifier that is the same word as keyword, then we should place quotes around it. We can have column named SELECT, if this identifier is inside of quotes. This also allows usage of spaces and special characters inside of our identifier.

SELECT 'value' as "SELECT.;' ";    

Identifiers can not start with % symbol.

SELECT 'value' as "%columnName";SELECT 'value' as "columnName";

Constants

String constants are delimited with single quotes like 'string data'. If our text contains single quotes then such single quotes should be doubled, like 'O''Connor'.

SELECT 'O''Connor' AS columnName;  

We can use UNICODE codes to create constants.

sql>SELECT U&'\0441\043F\0430\0441\0438\0431\043E' as "thank you";  

With UESCAPE it is possible to change default escape sign:

sql>SELECT U&'*0441*043F*0430*0441*0438*0431*043E' UESCAPE '*' as "thank you";  

Time Constants

These constants can be typed as strings, but will still be recognized as time constants.

'2014-02-03'CAST('2014-02-03' AS DATE)
'15:45:56'CAST ('15:45:56' AS TIME)
'2014-02-03 15:45:56'CAST ('2014-02-03 15:45:56' AS TIMESTAMP)

Special Characters

Inside of strings we can use these special characters.

\t  –  this will return TAB.
\n –  new line.
\r  –  carriage return.
\f  –  form feed.
\'  –  single quote.
\\  – backslash.

If we want to disable such behavior, we can use raw strings. We just type "R" before string, and escape sequences will be ignored.

Data

Data is expressed as scalar or a table. Scalars are constants, column references, operator results, function results, and subqueries that return one value. Column reference is written as "tableName.columnName". We can omit "tableName" and only write "columnName" if there is no ambiguity.

Table name is written as "schemaName.tableName". If there is no ambiguity, we can write only "tableName".

We can also reference tables and columns through their aliases.