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:
The part represents the actual part of the datetime to extract. These can include:
The following example shows how to use the extract function to fetch the hour from a timestamp:
TIMESTAMP '2022-02-14 20:38:40.24') AS HOUR;
The previous example should return the hour from the timestamp as follows:
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:
This should return as follows:
SQL Server Extract Time
In SQL Server, you can extract time from a timestamp using the cast function as shown in the example below:
The previous query should return as follows:
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.