As I started this project, I prepared a list of topics for my posts. One of them was “working with different data types” as there are differences between the different databases. However, it seems like the “date” datatype family deserves an entire post by itself, so this is it.
If this is the first time you see my posts about the DB comparison project, you are more than welcome to read the introduction post which includes links to the other posts in this series as well.
Why Dates
There are many differences in data types between the different databases, and I might write about it later in this series, but while numbers and text are pretty much standard in our daily lives, it seems that dates are not really. I decided to check how the different databases save dates, performing calculations on dates, and what basic functions they provide. Note that there is another related data type called INTERVAL, I won’t get into this one in this post, but I will mention it (INTERVAL is a data type that includes multiple elements, for example “2 days, 1 hour” or “2 months, 3 days, 10 hours, 5 minutes”).
This time I decided to start with PostgreSQL (without any reason, every post will have a different random order, just to make it a bit more interesting).
PostgreSQL
DATE/TIME Data Types
In PostgreSQL there are 5 date/time data types:
- DATE – this is a 4-byte data type that includes only a date without time of the day.
- TIME – this is an 8-byte data type that saves only the time of the day (without the date) and can be set to different precision up to 1 microsecond.
- TIME WITH TIME ZONE – is similar to TIME, but contains also the timezone and therefore require 12 bytes.
- TIMESTAMP – an 8-byte data type that contains both date and time of the day up to 1 microsecond of precision.
- TIMESTAMP WITH TIME ZONE – similar to TIMESTAMP but also includes the time zone information.
A few comments:
- How is it possible that TIME WITH TIME ZONE requires 12 bytes, while TIMESTAMP WITH TIME ZONE, which includes more data is only 8 bytes? I checked and this is not a documentation mistake, it really takes less space.
- How does a TIME WITH TIME ZONE works without a date? Apparently PostgreSQL supports TIME WITH TIME ZONE because the SQL Standard specifies that (and for some backward compatibility), but it indeed doesn’t make real sense and PostgreSQL recommend you using TIMESTAMP WITH TIME ZONE for any time zone related info. You can read more about it here.
- For date format, PostgreSQL is quite flexible. Any unambiguous format will be accepted (for example: 01-JAN-2021, or January 1, 2021), otherwise you can set the format using the “datestyle” setting (using the SET command, in the postgresql.conf file, or by setting the PGDATESTYLE environment variable).
You can read more about the DATE/TIME data types here.
Date/Time Functions
There are lots of different functions on dates, so I chose some major ones that I can also compare to other databases (see examples below):
- current_date() – returns the current date (without time).
- current_time() – returns the current time (without date).
- clock_timestamp() – returns the current date and time.
- current_timestamp() or now() – returns the current date and time with time zone as of the beginning of the current transaction.
- age(timestamp,timestamp) and age(timestamp) – when two parameters are provided, produces INTERVAL of the difference between two timestamps. If only one parameter is provided, it produces INTERVAL between the provided timestamp and the current date.
- date_part(text, timestamp) – returns a specific part of the timestamp based on the text (e.g. only the hour or only the day).
- date_trunc(text, timestamp) – returns a date truncated (rounded down) to a specific part (e.g. the whole hour or whole minute).
- make_date(…), make_time(…), make_timestamp(…), make_timestamptz(…) – these functions get parameters for the different parts of the data type and return the data in the relevant data type (for example: make_date(2021,1,3) will return a DATE datatype for January 3, 2021).
For a full list of DATE/TIME function, check the documentation.
Date/Time Calculations
Besides functions, we can operators between dates and numbers:
- date + integer or date – integer– adds or subtracts a number of days to a date and returns a DATE data types.
- date + time – returns a TIMESTAMP data type the represents the date provided and the time of the day provided.
- date – date – returns an INTEGER represented the number of days between the two dates.
- timestamp – timestamp – returns INTERVAL containing the difference between the timestamps.
- time – time – returns TIME containing the difference between the two time values.
- OVERLAPS – PostgreSQL supports the OVERLAPS SQL operator. It gets 2 date ranges (begin date and end date for each) and returns TRUE if there is an overlap between them or FALSE if there isn’t (see example below).
For a full list of DATE/TIME calculations, check the documentation.
Examples
To keep this post reasonably sized, I’ll only show a few examples here:
lirondb=# select make_date(2021,2,3),make_date(2021,2,3)+35;
make_date | ?column?
------------+------------
2021-02-03 | 2021-03-10
(1 row)
lirondb=# select now(),date_part('hour',now());
now | date_part
-------------------------------+-----------
2021-08-31 14:16:17.542526-07 | 14
(1 row)
lirondb=# select now(),date_trunc('minute',now());
now | date_trunc
-------------------------------+------------------------
2021-08-31 14:16:56.084114-07 | 2021-08-31 14:16:00-07
(1 row)
lirondb=# select age(make_timestamp(2020,09,05,5,0,0));
age
--------------------------
11 mons 25 days 19:00:00
(1 row)
lirondb=# select (make_date(2021,2,3),make_date(2021,3,3)) overlaps
lirondb-# (make_date(2021,1,1),make_date(2021,2,20));
overlaps
----------
t
(1 row)
SQL Server
DATE/TIME Data Types
SQL Server has 6 different data types for dates:
- TIME – this is a 3-5 bytes data types that contains only the time (no date) with accuracy of up to 100 nanoseconds
- DATE – this is 3 byte datatype for date only (no time)
- SMALLDATETIME – contains both date and time, but while the output format contains seconds, the datatype actually doesn’t contain seconds and the data is rounded to the nearest minute. The value for this data type ranges between 1900-01-01 2079-06-06 and it takes 3 bytes.
- DATETIME – a full date and time datatype up to 3 milliseconds accuracy. The lowest value for this data type is 1753-01-01 (largest value is 9999-12-31) and it takes 8 bytes.
- DATETIME2 – similar to DATETIME but with lowest value of 0001-01-01 (largest value is the same) and accuracy of 100 nanoseconds. It takes 6-8 bytes.
- DATETIMEOFFSET – is a DATETIME2 with time zone, therefore it takes 8-10 bytes.
Just one comment here:
- I don’t understand why there are so many different DATETIME with different accuracies? I’m sure the reason is backward compatibility, but still…
For the full documentation about DATE/TIME data types, click here.
Date/Time Functions
SQL Server has 6 different functions that return the current date/time:
- current_timestamp and getdate() – both return the current date and time in the DATETIME data type.
- getutcdate() – returns DATETIME of the current date and time, but at UTC timezone (even though the data type does not contain time zone information).
- sysdatetime() – returns the current date and time in the more accurate DATETIME2 data type.
- sysutcdatetime() – similar to sysdatetime() but shows the time at UTC timezone (even though the data type does not contain time zone information).
- sysdatetimeoffset() – returns the current date and time in the DATETIMEOFFSET data type (including the time zone information).
There are other functions to handle dates as well, including:
- datename(part,date) and datepart(part,date) – return the name in text (datename) or a number representing (datepart) a specific part of the date provided, such as “day”, “month” or “hour” (see examples below)
- day(date), month(date), year(date) – extracts the specific part of the date and returns as an integer.
- datefromparts(…), datetime2fromparts(…), datetimefromparts(…), datetimeopffsetfromparts(…), smalldatetimefromparts(…), timefromparts(…) – these are just like the “make*” function in PostgreSQL. Each gets parameters for the different parts of the data type and returns the data in the relevant data type (for example: datefromparts(2021,1,3) will return a DATE datatype for January 3, 2021).
- dateadd(part,number,date) – will add the “number” of “part” to the “date” and return DATETIME.
- eomonth(date,number) – will provide the last day of the month (in the same data type as the “date” provided) of the date plus number of months (optional).
For a full list of date/time related functions, check the documentation.
Date/Time Calculations
SQL Server documentation does not explain calculations on the date/time data types, but this is what I figured out:
- data type +/- integer – this adds a number of days to the date provided. However, it’s not supported for all data types. It works only for SMALLDATETIME, DATETIME, and DATETIME2.
- Tried to subtract dates from dates, but for TIME and DATE I got errors, for the others I got a date back that didn’t make sense to me.
Examples
2> create table t(f1 time, f2 date,f3 smalldatetime,f4 datetime,f5 datetimeoffset);
3> go
1> insert into t values(sysdatetimeoffset(),sysdatetimeoffset(),sysdatetimeoffset(),sysdatetimeoffset(),sysdatetimeoffset());
2> go
(1 rows affected)
1> select * from t;
2> go
f1 f2 f3 f4 f5
---------------------- ---------------- ------------------- ----------------------- ---------------------------------------------
16:28:17.1155588 2021-08-31 2021-08-31 16:28:00 2021-08-31 16:28:17.117 2021-08-31 16:28:17.1155588 -07:00
1> select getdate() as currdate,datename(day,getdate()) as dayofmonth,datename(weekday,getdate()) as dayofweek;
2> go
currdate dayofmonth dayofweek
----------------------- ------------------------------ ------------------------------
2021-08-31 16:44:24.007 31 Tuesday
(1 rows affected)
1> select getdate() as currdate,eomonth(getdate()) as endofmonth,eomonth(getdate(),3) as endofmonth2months;
2> go
currdate endofmonth endofmonth2months
----------------------- ---------------- -----------------
2021-08-31 16:46:15.610 2021-08-31 2021-11-30
(1 rows affected)
1> select getdate() as currdate,dateadd(hour,2,getdate()) add2hours,dateadd(month,2,getdate()) add2months;
2> go
currdate add2hours add2months
----------------------- ----------------------- -----------------------
2021-08-31 16:47:35.703 2021-08-31 18:47:35.703 2021-10-31 16:47:35.703
(1 rows affected)
Oracle
DATE/TIME Data Types
Oracle has 4 date/time data types:
- DATE – this is (unlike the previous two databases) includes date and time information (always) with accuracy of 1 second. This data type takes 7 bytes.
- TIMESTAMP – this is a more accurate data type and it includes date and time with configurable accuracy of up to 9 digits of fraction of a second (1 nanoseconds). This data type takes between 7 and 11 bytes.
- TIMESTAMP WITH TIMEZONE – same as TIMESTAMP but includes time zone data as well and takes 13 bytes.
- TIMESTAMP WITH LOCAL TIMEZONE – same as TIMESTAMP WITH TIME ZONE, with 2 exceptions: the data is normalized to the database time zone when it is stored in the disk, and the data is presented to clients based on their session time zone. It takes between 7 and 11 bytes.
And my comments on this:
- Why didn’t Oracle implemented “TIME” data type? We can emulate DATE (without time) if we set the DATE data type to always be midnight, but we cannot emulate TIME. If I only care about the time of the day, queries and inserts will have to include the same date which doesn’t make sense.
- Oracle also seems to be the only one that has TIMESTAMP WITH LOCAL TIMEZONE. I don’t understand why they added it, the TIMESTAMP WITH TIMEZONE basically has all the information to allow the same functionality.
For more information about Oracle’s data types check the documentation.
Date/Time Functions
There are quite a few functions in Oracle, here are some important ones:
- sysdate – returns the current date and time of the database server in DATE data type.
- systimestamp – returns the current date and time of the database server in TIMESTAMP WITH TIMEZONE data type.
- current_date – returns the current date and time according to the session time zone in DATE data type.
- current_timestamp – returns the current date and time according to the session time zone in TIMESTAMP WITH TIMEZONE data type.
- add_months(date,integer) – adds number of months to the provided date and returns DATE data type
- last_day(date) – returns the last day of the month of the provided date.
- to_char(date,format) – gets any date data type and returns its text representation based on the provided format.
- trunc(date,format) – will truncate (round down) the provided date to the provided part of the date.
Date/Time Calculations
Oracle supports calculations on dates as well:
- date + number or date – number– adds or subtracts a number of days to a date and returns a DATE data types (“number” can be non-integer)
- date – date – returns a NUMBER represented the number of days between the two dates (including the fraction of the day).
- timestamp – timestamp – returns INTERVAL containing the difference between the timestamps.
Examples
SQL> select sysdate,trunc(sysdate,'HH24'),last_day(sysdate) from dual;
SYSDATE TRUNC(SYSDATE,'HH24' LAST_DAY(SYSDATE)
-------------------- -------------------- --------------------
15-SEP-2021 16:45:50 15-SEP-2021 16:00:00 30-SEP-2021 16:45:50
SQL> select to_char(sysdate,'YYYY/MON/DD HH24:MI:SS Day') from dual;
TO_CHAR(SYSDATE,'YYYY/MON/DDHH24:MI:SSDAY')
------------------------------------------------------------------
2021/SEP/15 16:47:31 Wednesday
SQL> create table dates(f1 date,f2 timestamp);
Table created.
SQL> insert into dates values(sysdate,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dates;
F1 F2
-------------------- ------------------------------
15-SEP-2021 16:40:10 15-SEP-21 04.40.10.773729 PM
SQL> select sysdate,f1,sysdate-f1 from dates;
SYSDATE F1 SYSDATE-F1
--------- --------- ----------
15-SEP-21 15-SEP-21 .0003125
SQL> select systimestamp,f2,systimestamp-f2 from dates;
SYSTIMESTAMP F2 SYSTIMESTAMP-F2
--------------------------------------- ------------------------------- ----------------------------
15-SEP-21 04.43.38.305012 PM -07:00 15-SEP-21 04.40.10.773729 PM +000000000 00:03:27.531283
MariaDB
DATE/TIME Data Types
With MariaDB we have five different data types (4 are quite standard, but one is pretty unique):
- DATE – this is a 3-byte data type that includes only a date without time of the day.
- TIME – this is a 3 to 6 byte data type that saves only the time of the day (without the date) and can be set to different precision up to 1 microsecond.
- DATETIME – an 8 to 11 byte data type that contains both date and time of the day up to 1 microsecond of precision.
- TIMESTAMP – a 4 to 7 byte data type that also contains both date and time of the day up to 1 microsecond of precision. However, internally (unlike DATETIME) it saves the number of seconds since 1970-01-01 at midnight (UTC) and the date is calculated based on that number.
- YEAR – a 1-byte data type that only saves a year information.
Note that I didn’t mention timezones here. This is because MariaDB doesn’t support saving timezone data in the database. The only datatype that can actually work with timezones (in the database and not handled by the application), is TIMESTAMP, where MariaDB uses the client TZ information to convert the date and time to UTC and then saves it in the database. Any query performed on this column will be converted based on the querying client info and will be presented in the client timezone. All other data types are saved as-is without any conversion and without any TZ information.
For more information about date data types in MariaDB read here.
Date/Time Functions
- curdate() or current_date() – return the current date (without time).
- cur_time() or current_time() – return the current time (without date).
- current_timestamp() – return the current date and time.
- adddate(date,integer) – adds the number of days specified to the date provided.
- dayofweek(date), dayofmonth(date), dayofyear(date) – extract the number representing the day of the week/month/year from the provided date.
- hour(time), minute(time), second(time) – extract the relevant part of the time and returns it as a number
- str_to_date(string,format) – returns a DATETIME data type based on the string in the format provided
For a full list of functions, check here.
Date/Time Calculations
Apparently, you cannot perform date calculations with MariDB. I tried adding and subtracting DATE and TIMESTAMP datatype, in every combination it seems that MariaDB converted the date into a number (for example 2021-09-20 became 20,210,920) and then it performed simple math calculation. So for example, the difference between 2021-10-01 and 2021-09-30 is not 1 (1 day apart), but 71 which is 20211001-20210930.
See examples below.
Examples
MariaDB [test]> select curdate();
+------------+
| curdate() |
+------------+
| 2021-10-05 |
+------------+
1 row in set (0.000 sec)
MariaDB [test]> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:22:26 |
+-----------+
1 row in set (0.000 sec)
MariaDB [test]> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-10-05 14:35:21 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [test]> select curdate()+30;
+--------------+
| curdate()+30 |
+--------------+
| 20211035 |
+--------------+
1 row in set (0.000 sec)
MariaDB [test]> select adddate(curdate(),30);
+-----------------------+
| adddate(curdate(),30) |
+-----------------------+
| 2021-11-04 |
+-----------------------+
1 row in set (0.000 sec)
MariaDB [test]> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [test]> select str_to_date('05-may-2021 14:00:05','%d-%b-%Y %k:%i:%s');
+---------------------------------------------------------+
| str_to_date('05-may-2021 14:00:05','%d-%b-%Y %k:%i:%s') |
+---------------------------------------------------------+
| 2021-05-05 14:00:05 |
+---------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]> select str_to_date('05-may-2021 14:00:05','%d-%b-%Y %k:%i:%s')-str_to_date('05-jan-2021 14:00:05','%d-%b-%Y %k:%i:%s');
+-----------------------------------------------------------------------------------------------------------------+
| str_to_date('05-may-2021 14:00:05','%d-%b-%Y %k:%i:%s')-str_to_date('05-jan-2021 14:00:05','%d-%b-%Y %k:%i:%s') |
+-----------------------------------------------------------------------------------------------------------------+
| 400000000 |
+-----------------------------------------------------------------------------------------------------------------+
Conclusions
Here are some major differences between the databases:
- Oracle is the only one that doesn’t have a date data type without time and a time data type without date.
- MariaDB is the only one that has a data types for year only, without anything else.
- MariaDB is the only one that doesn’t have a data type that saves timezone information as well
- Calculations done on these data types vary a lot between databases. Some don’t support this at all (MariDB), some partially support them (SQL Server), and some fully support this with whole number only (PostgreSQL) or with even fractions (Oracle)
Final Notes
It took a long time to finish this post. I have to admit that I expected some differences between the databases, but I didn’t think it would be that much. Working with dates apparently is completely un-standardized, and if you move from one database to another you can easily fall into some pitfalls without understanding what’s going on. I expected the functions to be different, and even some data type names, but the fact that some databases have data types that don’t exist at all in others, or behave completely differently, is challenging.
I hope you enjoyed this post. Please let me know if you have any other interesting differences between different databases, or leave a comment below if you have anything to say about this post.
If you’d like to get an email when I publish a new post, fill in your email and click the “subscribe” button below:
2 thoughts on “DB Comparison Project: Working with Dates”