Retrieving the Current Date and Time in SQL

1. Overview

At times, we’re required to retrieve the current date and time in SQL for various reasons. These include time-stamping records and synchronizing data across different time zones.

In this tutorial, we’ll discuss how to obtain the current date and time in MySQL, SQL Server, and PostgreSQL.

Although we don’t require any specific table or column for this tutorial, for demonstration purposes, we’ll use the University database.

2. MySQL

In this section, we’ll learn four different MySQL functions for retrieving the current date and time.

2.1. Using NOW

We can use the NOW function to get the current date and time in the format YYY-MM-DD HH:MM:SS:

SELECT NOW();

This statement returns the exact time when the query begins execution:

NOW()
---------------------------
2024-10-17 17:08:01

2.2. Using CURRENT_TIMESTAMP and LOCALTIMESTAMP

We can interchangeably use CURRENT_TIMESTAMP and LOCALTIMESTAMP instead of the NOW function to get the current date and time, as they’re aliases.

Let’s use LOCALTIMESTAMP in our query:

SELECT LOCALTIMESTAMP();

We can also use the CURRENT_TIMESTAMP function:

SELECT CURRENT_TIMESTAMP();

Moreover, we can also provide a numeric value between 1 and 6 as an optional parameter to the CURRENT_TIMESTAMP function to obtain the current date and time down to milliseconds.

2.3. Using SYSDATE

We can use the SYSDATE function to retrieve the current date and time in MySQL. This function is useful when we need to get the exact time of function execution:

SELECT SYSDATE();

Generally, the output of NOW and SYSDATE can be slightly different. They capture the current date and time at different points during the execution of a SQL statement.

3. PostgreSQL

Furthermore, let’s learn three PostgreSQL functions for getting the current time and date.

3.1. Using NOW and CURRENT_TIMESTAMP

We can use the NOW and CURRENT_TIMESTAMP functions to get the current date and time in PostgreSQL, similar to MySQL. However, we also get the time zone in the result. To eliminate the time zone, we can run:

-- using NOW function
SELECT NOW()::TIMESTAMP;

-- using CURRENT_TIMESTAMP function
SELECT CURRENT_TIMESTAMP::TIMESTAMP;

The syntax ::TIMESTAMP will explicitly cast the results into the TIMESTAMP data type. Moreover, we don’t require parentheses after the CURRENT_TIMESTAMP function because it’s a system-defined constant function.

Let’s observe the result of the NOW function query:

 now
----------------------------
 2024-10-17 17:58:46.475946
1 row

3.2. Using LOCALTIMESTAMP

Similar to MySQL, we can use the LOCALTIMESTAMP function to retrieve the current date and time:

SELECT LOCALTIMESTAMP;

This function doesn’t require parentheses.

4. SQL Server

Finally, let’s discuss five functions to obtain the current date and time in SQL Server.

4.1. Using GETDATE and CURRENT_TIMESTAMP

The GETDATE and CURRENT_TIMESTAMP functions are aliases for each other that extract the current local date and time:

-- GETDATE function
SELECT GETDATE();

-- CURRENT_TIMESTAMP function
SELECT CURRENT_TIMESTAMP;

We get the same output for both queries:

2024-10-18 17:09:34.360

4.2. Using SYSDATETIME

In SQL Server, we can also use the SYSDATETIME function to retrieve the local date and time with precision up to nanoseconds:

SELECT SYSDATETIME();

Here, in the output, we can observe the higher precision in time:

2024-10-18 17:33:06.9419722

4.3. Using GETUTCDATE and SYSUTCDATETIME

In SQL Server, we can also retrieve the current date and time in UTC format using GETUTCDATE and SYSUTCDATETIME:

-- get current date and time in UTC format with millisecond precision
SELECT GETUTCDATE();

-- get current date and time in UTC format with nanosecond precision
SELECT SYSUTCDATETIME();

5. Conclusion

In this article, we explored how to retrieve the current date and time using SQL across different databases, including SQL Server, MySQL, and PostgreSQL.