Working with SQL Timestamp, Date and Time Data Types.
Working with Date and Time Types in SQL.
For more 🚀
Listen to me on
TL;DR: Working with Time in SQL is one of the most common tasks that people are seeking help for. In this article I will cover the most common ways to work with time in SQL.
SQL Timestamp, Date and Time Data Types
The SQL standard defines the datetime datatype which can be further specified using the following descriptors:
- TIME WITH TIME ZONE
- TIMESTAMP WITH TIME ZONE
Together with the value of the time fractional seconds precision if the descriptor is one of:
- TIME WITH TIME ZONE
- TIMESTAMP WITH TIME ZONE
SQL also defines intervals in a similar way. But there’s only one possible type desceriptor, in this case INTERVAL.
Together with, an indication of whether the interval is a year-month or day-time interval and finally the interval qualifier that describes the precision of the interval data type.
Things to Remember
👉 Datetimes only have absolute meaning in the context of additional information.
So, Unless that time zone specifier, and its meaning, is known, the meaning of the datetime value is ambiguous.
Therefore, datetime data types that contain time fields (TIME and TIMESTAMP) are maintained in Universal Coordinated Time (UTC), with an explicit or implied time zone part.
👉 The time zone part is an interval specifying the difference between UTC and the actual date and time in the time zone represented by the time or timestamp data item.
👉 Items of type datetime are mutually comparable only if they have the same datetime fields.
👉 There is an ordering of the significance of datetime fields. This is, from most significant to least significant: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
Ok, that’s all you need to know! (Joking, there’s more) but the above statements are helpful to always keep in mind when working with time in SQL.
Operating on Datetime types
The basic arithmetic operators are supported for Datetime type, although it’s always a good idea to check the documentation of the database you are using to check the exact semantics implemented for the operator.
The following operators are the most commonly found ones:
- Addition (+)
- date + integer
- date + interval
- date + time
- interval + interval
- timestamp + interval
- time + interval
- Subtraction (-)
- -interval (Negation)
- date - date
- date - integer
- date - interval
- time - time
- time - interval
- timestamp - interval
- interval - interval
- timestamp - timestamp
- Multiplication (*)
- interval * double precision
- Division (/)
- interval / double precision
Again, the most important thing is to check with the database documentation on the semantics of the above operations.
For example, what should be the return type of each operation?
Date and Time Functions
There are some functions are that more important than others or at least you are going to need them more often. These are:
👉 First the popular
trunc(field, source) function. Where source is a value expression of a datetime related type and field is used
to define the precision of the truncation. Some examples of valid field values from postgres are:
As usual, check your documentation!
👉 Then we have
now() which returns the current timestamp, usually at the beginning of the query execution.
There also a number of other similar functions, like:
- and many more…
extract(field from source) function is used to retrieve subfields such as year or hour from date/time values.
The extract function is primarily intended for computational processing.
date_part(field, source) function extracts a part of the date, timestamp, or interval.
date_format(expr, fmt) is used to format a date time value using the format specified by fmt.
Formatting is a bit of a complicated matter as there’s a lot of flexibility on how a date can be formatted.
There’s a “language” that is used to define patterns for the formatting. This language is at least similar among the different databases and as I’ve said many times, you should check the documentation!
👉 Finaly there are the casting expressions like
cast(expr as type) that are used to cast between different datetime types and others.
For example casting a string into a date or timestamp or a datetime to timestamp etc.
The above are some of the most commonly used functions and operators when working with time in SQL. Most database systems offer many more for convenience but the above should be enough to cover most of the use cases.
This is where things starts getting more complicated when working with dates and times in SQL.
Windows are important as they allow to define partitions over your data and perform aggregations.
Consider for example the case of calculating the Monthly Recurrent Revenue. To do that, you first need to partition your data by month and then calculate the sum of the revenue for each month.
The most common way of defining a window is using the
OVER clause. For example:
SELECT date_trunc('month', date) AS month, SUM(revenue) OVER (PARTITION BY date_trunc('month', date)) AS mrr FROM transactions
The above query will calculate the MRR for each month and it’s a good example of how powerful the combination of windows and datetime functions are.
There are other types of windows available but they can be grouped in two main categories.
- Ranking windows
- Aggregate (or analytic) windows
The most common example of a ranking window function is
rank() which returns the rank of a value compared to all values in the partition.
A common example of an analytic window function is
lag(args) which returns the value of expr from a preceding row within the partition.
Again, you will find examples and more details in the documentation of your database system.
Working with datetime in SQL can easily get complicated, especially when we start working with formatting and windows.
But the most common tasks associated with time in SQL are not that complicated. There’s a small number of data types and a large number of helper functions that can help a lot.
Below you can find a list of links to the documentation of some well known OLAP and OLTP databases.
Databricks - Spark Date-Time & Functions
Databricks - Spark Windows (ranking)
Databricks - Spark Windows (analytics)