SQL Date and Time Functions in 5 Popular SQL Dialects

Are you confused by all the date and time functions used across different SQL dialects? In this article, I summarize the date and time data types used in PostgreSQL, Oracle, SQLite, MySQL, and T-SQL. I also provide examples with the key SQL date and time functions used across these dialects. It’s time to become date and time gurus!

Do you want to calculate how often employees are running late for work? Or how long it takes to complete an order? There are many cases when data analysts need to perform calculations on date and time-related values in SQL. However, date and time data types and functions differ significantly across SQL dialects. The queries will look very different depending on whether you write them in, say, PostgreSQL or SQL Server.

In this article, I cover the basics of dealing with dates and times across different SQL dialects. I also provide examples of key date and time functions.

If you want to skip to a certain part, here are the SQL dialects covered in this article:

• PostgreSQL
• Oracle
• SQLite
• MySQL
• T-SQL (MS SQL Server)

You can also start learning date and time data types today with the Data Types in SQL course. This interactive course covers data types that work in all popular relational database management systems, including SQL Server, MySQL, Oracle, and PostgreSQL.

SQL Date and Time Functions Across Different Dialects

It’s always easier to get new information through real-world use cases. To understand how dates and times can be processed across different SQL dialects, I suggest using the following table that shows trips planned for 2022. Here we have both dates and timestamps with a time zone offset.

Now let’s see how we can deal with this data in different SQL dialects.

PostgreSQL Date and Time Functions

There are four data types that process dates and times in PostgreSQL:

• The date data type is used to store dates without the exact time (e.g. '2022-21-03').
• The time data type allows us to store time without a date (e.g. '8:34:59'). By default, the time data type doesn’t include information on the time zone. If you need to specify the time zone, you’ll need to use time with time zone. However, it is recommended to use the next data type when dealing with time zones.
• The timestamp data type is very useful in the practical setting, as it allows us to store the full timestamp – the dates with the exact time (e.g. '2022-07-30 06:55:34'). The precision can be up to 1 microsecond. As with the time data type, if you want to include information on the time zone, you’ll need to use timestamp with time zone.
• The interval data type is used to store information on time intervals (i.e. duration). You can restrict the set of stored fields by adding a corresponding phrase (e.g. YEAR, DAY, YEAR TO MONTH, HOUR TO SECOND); you can get more details in PostgreSQL’s documentation. These intervals can be added or subtracted from the data types defined above.

You can read more on these and other PostgreSQL data types in this article; let’s move on to a practical use case.

To create our trips table in PostgreSQL, we use the following code:

CREATE TABLE trips(
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 8:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Note that we use the date data type to store information on the departure and return dates. We also use timestamp with time zone to store departure and arrival times. This data type allows us to store date, time, and time zone information; as all our trips cross several time zones, this is important.

To practice PostgreSQL date and time functions, let’s say that we want to find out:

1. The duration of our trip in days.
2. The duration of our flight from home in New York City to the destination city.
3. The date when we need to start preparing for the trip, which we can imagine to be around 14 days.

We want this information for each trip in our table. Here is how to find it using PostgreSQL:

SELECT 
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - INTERVAL '14 DAYS' AS prep_date
FROM trips;

Note that we have added +1 to include both departure and arrival days to our trip duration. Here are the results:

There are many more things you can do with date and time in PostgreSQL. In the following table, I have summarized how to:

• Get the current date and the current time.
• Subtract dates/times.
• Add/subtract intervals.
• Extract certain parts of the date/time.
• Create a date/time from its parts.
• Truncate dates.
• Convert strings into date/time objects.
• Change date/time objects into strings with specific formatting.

For more details on each of the above functions, check out PostgreSQL’s documentation on date/time types, date/time functions and operators, and date/time formatting functions.

Oracle Date and Time Functions

Here are the data types for processing dates and times in the Oracle database:

• DATE. This data type stores date and time information, including century, year, month, date, hour, minute, and second. If a time component is not specified, then the default time is midnight.
• The TIMESTAMP data type is an extension of the DATE data type, as it also stores fractional seconds.
  • TIMESTAMP WITH TIME ZONE is a variant of the TIMESTAMP data type. It stores a time zone offset or time zone region name.
  • TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. Instead of storing a time zone offset as a part of the column data, the time zone information is simply normalized to the database time zone, i.e. Oracle returns it in the users' local session time zone.
• The INTERVAL YEAR TO MONTH data type stores a period of time using the YEAR and MONTH
• The INTERVAL DAY TO SECOND data type stores a period of time in terms of days, hours, minutes, and seconds.

You can read more on Oracle datetime data types in the Oracle documentation; we’re moving to our practical use case. Let’s now create the trips table in the Oracle database.

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);
   
INSERT INTO trips
VALUES (1, 'San Francisco', DATE'2022-03-21', DATE'2022-03-26', TIMESTAMP'2022-03-21 8:00:00 -04:00', TIMESTAMP'2022-03-21 11:14:00 -07:00');

INSERT INTO trips
VALUES (2, 'London', DATE'2022-06-03', DATE'2022-06-10', TIMESTAMP'2022-06-03 19:00:00 -04:00', TIMESTAMP'2022-06-04 07:10:00 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', DATE'2022-07-28', DATE'2022-08-10', TIMESTAMP'2022-07-28 15:00:00 -04:00', TIMESTAMP'2022-07-30 06:55:00 +10:00');

As you can see, we are using the DATE data type for our departure and return dates. (We don’t need time zone information or precision up to fractional seconds here.) We’re using TIME WITH TIME ZONE for our departure and arrival times, as we have data across different time zones.

Note that when we’re inserting values, we specify the data type for each value. Alternatively, we could use the Oracle default format without specifying the data type (e.g. '21-MAR-2022' and '21-MAR-2022 8.00.00 AM -04.00'). You may change the default formats using the NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, and NLS_TIMESTAMP_TZ_FORMAT initialization parameters. You can find more information in Oracle’s documentation.

For each destination, we again want to find out the duration of the trip in days (including departure and arrival days), the duration of the flight to the target city, and the date when we need to start preparing for the trip, assuming we want to start 14 days ahead.

Here is how to find this information in Oracle:

SELECT
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - 14 AS prep_date
FROM trips;

There are many more things you can do with dates and times in Oracle. The following table summarizes how to:

• Get the current date and the current time.
• Subtract dates and subtract times.
• Add/subtract intervals.
• Extract certain parts of the date/time.
• Truncate dates.
• Convert strings into date/time objects.
• Change date/time objects into strings with specific formatting.

For more details on each of the above functions – and some other useful date and time functions in Oracle – check out this article and the Oracle documentation.

SQLite Date and Time Functions

SQLite does not have particular data types for storing dates and times. However, SQLite data and time functions can help you store dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO 8601 strings ('YYYY-MM-DD HH:MM:SS.SSS').
• REAL as the number of days since noon in Greenwich on November 24, 4714 B.C.
• INTEGER as the number of seconds since 1970-01-01 00:00:00 UTC.

Here are the SQLite date and time functions:

• The date() function returns the date in the format YYYY-MM-DD.
• The time() function returns the time in the format HH:MM:SS.
• The datetime() function returns the timestamp in the format YYYY-MM-DD HH:MM:SS.
• The julianday() function returns the Julian Day, i.e. the number of days since noon in Greenwich, England on November 24, 4714 B.C.
• The strftime() function returns the date formatted according to the format string specified as the first argument.

To create our trips table in SQLite, we can use the following code:

CREATE TABLE trips (
    id int,
    destination text,
    departure_date text,
    return_date text,
    departure_timestamp text,
    arrival_timestamp text
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Note that we use the TEXT data type to store all our dates and times. Then, we insert values by specifying dates and times in the usual format, even including time zones when necessary.

Now we want to practice SQLite date and time functions by calculating our trip duration in days (including departure and arrival days), the flight duration in hours, and the date when we need to start preparing for the trip, which is the usual 14 days out.

Here is how to calculate these in SQLite:

SELECT
  destination,
  julianday(return_date) - julianday(departure_date) + 1 AS trip_duration,
  (julianday(arrival_timestamp) - julianday(departure_timestamp)) * 24 AS flight_duration,
  date(departure_date, '-14 days') AS prep_date
FROM trips;

In this query, we used the julianday() function when calculating the difference between two dates/timestamps. This function returns the number of days, which is what we expect for the trip duration. The flight duration is better presented in hours – thus, we multiply the result by 24 to get the flight duration in hours. Note also how elegantly the date() function allows us to add/subtract days from a date value. You can similarly add/subtract years, months, hours, minutes, and seconds.

Let’s see what else we can do with dates and times in SQLite. In the following table, I have summarized how to:

• Get the current date and the current time.
• Subtract dates.
• Subtract times.
• Add/subtract intervals.
• Extract certain parts of the date/time.
• Truncate dates.
• Convert date/time objects into strings with specific formatting.

In contrast to the tables we have for other SQL dialects, here the columns do not correspond to specific data types. They simply include examples for processing (1) dates, (2) times, and dates and times together (i.e. timestamps).

For more details on the SQLite date and time functions, check the SQLite documentation.

MySQL Date and Time Functions

There are five data types to process dates and times in MySQL:

• The DATE data type is used for values with a date part but no time part.
• The DATETIME data type is used for values that contain both date and time parts. You can also have fractional seconds when inserting the DATETIME values into the table. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• The TIMESTAMP data type is also used for values that contain both date and time parts. It also supports fractional seconds. Additionally, starting from MySQL 8.0.19, this data type provides full support for time zone information. The supported range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
• The TIME data type is used to represent a time of day and also the elapsed time or a time interval between two events. TIME values may range from '-838:59:59' to '838:59:59'. Fractional seconds are supported.
• The YEAR data type is used to represent year values. The supported range is 1901 to 2155. Note also that YEAR accepts values in a variety of formats, e.g. '2021', 2021, '21', 21.

You can read more about date and time data types in MySQL in our in-depth guide and in MySQL documentation.

Now let’s repeat our example with trips, but this time in MySQL. We start by creating the trips table:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp,
    arrival_timestamp timestamp
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00:00-04:00', '2022-03-21 11:14:00-07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00:00-04:00', '2022-06-04 07:10:00+01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00:00-04:00', '2022-07-30 06:55:00+10:00');

As you can see, we use the DATE data type to store departure and return dates. For departure and arrival times, we have chosen the TIMESTAMP data type, as we want to preserve information on the time zone.

To calculate trip duration (including departure and arrival days), flight duration, and the date to start preparation (14 days in advance), we can use the following MySQL query:

SELECT
    destination, 
    DATEDIFF(return_date, departure_date) + 1 AS trip_duration, 
    TIMEDIFF(arrival_timestamp, departure_timestamp) AS flight_duration,
    DATE_SUB(departure_timestamp, INTERVAL 14 DAY) AS prep_date
FROM trips;

As you can see from this query, there are some very useful MySQL functions that process dates and times. In fact, there are many such functions. To get a glimpse of what you can do with dates and times in MySQL, check out the following table and learn how to:

• Get the current date and the current time.
• Subtract dates and times.
• Add/subtract intervals.
• Extract certain parts of the date/time.
• Create a date/time from its parts.
• Convert strings into date/time objects.
• Change the formatting of date/time objects.

For more details on each of the above functions, check out MySQL’s documentation on date and time functions.

T-SQL Date and Time Functions

Finally, let’s cover the date and time functions you should know if you work in MS SQL Server and use Transact-SQL (T-SQL).

First of all, here are the date and time data types supported by T-SQL:

• The time data type is used to store time values, including fractional seconds.
• The date data type is used to store date values without time parts.
• The smalldatetime data type is used in T-SQL to store dates and times in the range of 1900-01-01 through 2079-06-06, with an accuracy of up to 1 minute.
• The datetime data type can store dates and times in the range of 1753-01-01 through 9999-12-31, with an accuracy of up to 0.00333 seconds.
• T-SQL also has the datetime2 data type. It stores values in an even wider range (0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999) and defines fractional seconds up to 100 nanoseconds.
• Finally, the datetimeoffset data type stores dates and times with the time zone offset. It has the same range and accuracy as the datetime2 data type.

You can read more about data and time data types in Transact-SQL here.

And now it’s time to repeat our trip example with T-SQL. As usual, we start by creating the trips table:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp datetimeoffset,
    arrival_timestamp datetimeoffset
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Note that we use (1) the date data type to store departure and return dates, and (2) the datetimeoffset data type to store departure and arrival times (to preserve time zone information).

Now let’s calculate trip duration (including departure and arrival days), flight duration, and the date to start preparation (14 days in advance) using T-SQL:

SELECT
    destination, 
    DATEDIFF(day, departure_date, return_date) + 1 AS trip_duration, 
    DATEDIFF(hour, departure_timestamp, arrival_timestamp) AS flight_duration,
    DATEADD(day, -14, departure_timestamp) AS prep_date
FROM trips;

As you can see from this example, T-SQL has the DATEDIFF() function that processes not only dates but also time and datetime values. However, when calculating the flight duration, we were able to get only the whole number of hours instead of the exact interval with minutes. If you need more precise information, you can always select another date part for this function (e.g. minute to get flight duration in minutes). Read this article to see how you can process the output of this function to get the interval in the required format.

The following table summarizes some other operations you can do with dates and times in T-SQL:

• Get the current date and the current time.
• Subtract dates and times.
• Add/subtract intervals.
• Extract certain parts of the date/time (e.g. month, minute).
• Create date/time from its parts.
• Convert strings into date/time objects.
• Change the date and time formats.

As you can see from this example, T-SQL has the DATEDIFF() function that processes not only dates but also time and datetime values. However, when calculating the flight duration, we were able to get only the whole number of hours instead of the exact interval with minutes. If you need more precise information, you can always select another date part for this function (e.g. minute to get flight duration in minutes). Read this article to see how you can process the output of this function to get the interval in the required format.

The following table summarizes some other operations you can do with dates and times in T-SQL:

• Get the current date and the current time.
• Subtract dates and times.
• Add/subtract intervals.
• Extract certain parts of the date/time (e.g. month, minute).
• Create date/time from its parts.
• Convert strings into date/time objects.
• Change the date and time formats.

For more details on SQL Server date and time functions, see the T-SQL documentation.

Let’s Practice SQL Date and Time Functions!

I hope this article provided you with a general understanding of how dates and times can be processed across different SQL dialects. Now it’s time to practice!

I recommend starting with an interactive course. You can get comprehensive knowledge about SQL data and time functions, practice these functions with real-world examples, and get some guidance when you are stuck:

• Standard SQL Functions includes 211 coding challenges. You’ll practice the standard SQL functions used in processing text data, numeric data, dates and times, and more.
• Data Types in SQL includes 89 interactive exercises. It introduces you to common data types in SQL Server, MySQL, Oracle, and PostgreSQL.

If you want to get a comprehensive knowledge of SQL starting from zero programming experience, I recommend the SQL from A to Z learning track. It includes 7 interactive courses covering writing simple queries, combining data from multiple tables, writing complex SQL queries with subqueries, and using common table expressions, window functions, and more.

Thanks for reading, and happy learning.