Project

General

Profile

Wiki » Documentation » Internals »

Storage of ABL Datatypes in SQL Databases

Comparative SQL Type Mapping

The ABL data types are mapped to each SQL dialects as follows:

ABL Data Type H2 PostgreSQL MariaDb SQL Server Notes
blob blob oid blob varbinary(MAX)
character varchar_casesensitive or
varchar_ignorecase 		text varchar(N) varchar(8000) The data types in H2 are customized in FWD build.
N represents the declared space MAX-WIDTH / SQL_WIDTH in the definition of the field.
clob clob text mediumtext varchar(MAX)
comhandle varchar text text varchar(8000) Only used with TEMP-TABLEs
date date date date date
datetime timestamp timestamp datetime(3) datetime2
datetime-tz timestamp with time zone +
integer		 timestamp with time zone +
int4 		timestamp(3) +
integer		 datetimeoffset +
int 		Requires two SQL fields for all dialects. See below.
decimal numeric(50,N) numeric(50,N) decimal(50, N) numeric(38,N) N represents the precision given by DECIMALS in field definition.
The SQL Server dialect does not support 50 digits left of decimal point.
handle bigint int8 bigint bigint Only used with TEMP-TABLEs
integer integer int4 integer int
int64 bigint int8 bigint bigint
logical boolean bool boolean bit
object bigint int8 bigint bigint Only used with TEMP-TABLEs
recid integer int4 bigint int
raw binary bytea varbinary(N) binary
rowid bigint int8 bigint bigint Might be replaced in the future with a UUIDs.

Support for datetime-tz Data Type

Unfortunately, not all SQL dialect have data types which models a time-zoned instant, and even if they do, the way it is implemented is dialect specific:
  • H2: has the best support, using its timestamp with time zone. The dtz values are stored in database and retrieved without any loss. The additional integer field is really not needed here, but it is kept in order to have consistence with the other dialects;
  • PSQL: although the data type is named timestamp with time zone, it will NOT save the time instant and the TZ offset to be able to retrieve it binary identical to what was saved. Instead it will perform a time 'roll' to a specific timezone offset, for all values stored in database. This makes it easy to compare them, but the original time offset is lost in the process. This reference time-zone is, by default, the server local time-zone, but it can be set in postgresql.conf. More details here.
  • MariaDb: when timestamp values are stored in MariaDb database, they are automatically converted to UTC, but when extracted back, they are converted to the session's time zone.

In 4GL, the datetime-tz represents a datetime instance at a specific time zone offset and is written as text according to ISO8601: yyyy-MM-ddTHH:mm:ss.ttt+/-nn:mm. Semantically, two or more of these are equals if they 'happened' at the same time, regardless of the timezone offset they were observed. This is actually natural: if two people from countries from different TZ offsets communicate at the same instant, regardless if one sees 14:25+04:00 and the other 06:25-4:00. Rolling both these clocks to +00:00, we obtain 10:25 UTC.

From the DST point of view, these values are transparent. The UTC does not use DST. In the case of 14:25+04:00, the +4 hours could represent either:
  • a 4 hour positive time-zone offset from UTC in winter time;
  • a 3 hour positive time-zone offset from UTC in a +1 summer time, and even
  • a 2 hour positive time-zone offset from UTC in a +2 summer time.
    It does not matter which of the above combination one is, all of them are the same instant.

To illustrate this, I created the following 4GL test-case. It uses a very simple table:

ADD TABLE "dtz-test" 
  DUMP-NAME "dtz-test" 

ADD FIELD "dtz" OF "dtz-test" AS DATETIME-TZ
  INITIAL ?
  POSITION 2
  ORDER 10
And the following procedure:
CREATE dtz-test. dtz-test.dtz = 2023-12-25T00:01:02.003+00:00. RELEASE dtz-test.
CREATE dtz-test. dtz-test.dtz = 2023-12-25T03:01:02.003+03:00. RELEASE dtz-test.
CREATE dtz-test. dtz-test.dtz = 2023-12-24T21:01:02.003-03:00. RELEASE dtz-test. 

DEFINE VARIABLE ref AS DATETIME-TZ INITIAL 2023-12-25T00:01:02.003+00:00.

FOR EACH dtz-test WHERE dtz-test.dtz = ref:
   MESSAGE dtz-test.dtz.
END.

It will print, as expected: 

12/25/2023 00:01:02.003+00:00
12/25/2023 03:01:02.003+03:00
12/24/2023 21:01:02.003-03:00

However, if we are looking at the database content (MariaDb dialect used here, but PostGreSQL is very similar) we will see:

> show create table dtz_test;
CREATE TABLE `dtz_test` (
  `recid` bigint(20) NOT NULL,
  `dtz` timestamp(3) NULL DEFAULT NULL,
  `dtz_offset` int(11) DEFAULT NULL,
  PRIMARY KEY (`recid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

> select * from dtz_test;
+-------+-------------------------+------------+
| recid | dtz                     | dtz_offset |
+-------+-------------------------+------------+
| 19023 | 2023-12-25 02:01:02.003 |          0 |
| 19024 | 2023-12-25 02:01:02.003 |        180 |
| 19025 | 2023-12-25 02:01:02.003 |       -180 |
+-------+-------------------------+------------+

We can see all the dtz values are actually identical in database, and the values are represented with timezone offset as +2h (which is actually my timezone). The SQL driver rolled all three values we created in our testcase to a common timezone. This is OK, as they indeed represent the same instant and they are semantically equal.

However, to see the correct output printed in 4GL test application, FWD needs additional information to restore the original data. This is the moment when dtz_offset enter the scene. It was saved with the dtz, and now what FWD have to do is to roll the value fetched from database to match the original offsets. This is actually a simple procedure, but requires two steps:
  1. roll to UTC. The dtz value is a datetime-tz and has a time offset. In all cases, 2023-12-25 02:01:02.003 is using my local timezone +2h so the 2 hours must be subtracted, so we obtain: 2023-12-25 00:01:02.003
  2. roll to original offset. Simply, the number of minutes from dtz_offset is added and the values we started with are back.
Observations:
  • in case of 19023, the second operation is a NOP;
  • if a value was saved with my local time, it would appear unchanged in database. More than that, this step would be the reverse of the former, the amount of minutes subtracted would have been added back now. There is no specific handling in this case but we could optimize this case to skip the roll when the original offset matched the current timezone;
  • the above operation works correctly regardless of the reference offset is used by the SQL server. Even in case of H2, where the dtz values are not changed, the roll algorithm is just a NOP.