Introduction to SQL Timestamp, Date and Time Data Types.

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:

• 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.

References

Databricks - Spark Date-Time & Functions

Databricks - Spark Windows (ranking)

Databricks - Spark Windows (analytics)

Snowflake Types

Snowflake Windows

PostgreSQL Functions

PostgreSQL Windows

Clickhouse Functions

Clickhouse Windows