6. 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. One of those time zones can be designated as “local time zone” and such time zone will be used as default time zone, when connection doesn’t have time zone stated.

If we want, we can change our connection time zone setting by issuing statement “SET TIME ZONE INTERVAL ‘1’ HOUR TO MINUTE”.

Leave a Comment

Your email address will not be published. Required fields are marked *