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.

mumf in time

SQL Timestamp, Date and Time Data Types

The SQL standard defines the datetime datatype which can be further specified using the following descriptors:

DATE
TIME
TIMESTAMP
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
TIMESTAMP
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:

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

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:

current_date
current_time
current_timestamp
current_time(precision)
and many more…

👉 The 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.

👉 The 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.

Windows

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.

Conclusion

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.