SQL Remove Time from DateTime

When working with databases, you will encounter instances where you need to extract a specific part of a date. For example, you may need to remove the day from a DateTime.

This article will explore how to extract time from a DateTime in Standard SQL.

SQL Extract Function

The first method you can extract time from DateTime is the extract() function. The function takes the part to pull DateTime or Date or Timestamp as arguments.

The function syntax is shown below:

EXTRACT(part FROM expression);

The part represents the actual part of the datetime to extract. These can include:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

Example Usage

The following example shows how to use the extract function to fetch the hour from a timestamp:

SELECT
EXTRACT(HOUR
FROM
TIMESTAMP '2022-02-14 20:38:40.24') AS HOUR;

The previous example should return the hour from the timestamp as follows:

HOUR
20

You can join multiple select statements for minutes and seconds to create the time from the timestamp.

PostgreSQL Extract Time

Various database engines implement custom functions for extracting parts from timestamps. For example, in PostgreSQL, you can extract time from timestamp as shown below:

SELECT '2022-04-02 20:43:32'::TIMESTAMP::TIME;

This should return as follows:

TIME
----------
20:43:32
(1 ROW)

SQL Server Extract Time

In SQL Server, you can extract time from a timestamp using the cast function as shown in the example below:

SELECT CAST(‘2022–04–02 20:43:32.600’ AS TIME);

The previous query should return as follows:

TIME
----------
20:43:32.60000
(1 ROW)

Conclusion

This article describes various methods of extracting time from a DateTime and timestamp in SQL. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.