PostgreSQL provides a timestamp data type to store date and time data in a table. The PostgreSQL Extract function is used for extracting various date and time fields from timestamp data. You can extract day, month, year, and time information from a timestamp field. The extract function only returns the numeric data. To do more formatting on the timestamp column, use the TO_CHAR function in PostgreSQL.
Table of Contents
Syntax
Below is the syntax of the Extract function.
EXTRACT(field from source)A field name is a string that selects which field to extract from a timestamp column. In the Source field, you can give the name of the table column; the column name must be of the timestamp, time, or interval data.
Below are the most commonly used valid values for field names.
| Field Name | Description |
| day | Day of Month (1-31) |
| month | Month number (1–12) for timestamp, for interval (0–12) |
| year | Year (4-digits, like 2024) |
| hour | Hour field (0-23) |
| minute | Minute field (0-59) |
| second | Seconds return a decimal value. The fraction part is for milliseconds. |
| dow | Day of the Week (0-Sunday) |
| doy | Day of the year (1-365), Leap Year (1-366) |
| quarter | Quarter of the Year (1-4) |
| week | Week of the Year |
To get all field names, refer to the official documentation of the PostgreSQL Extract function.
Extract Day from timestamp
Day of Month (1-31)
To extract day In the below example, we are extracting day information from a timestamp field.
SELECT EXTRACT(day FROM TIMESTAMP '2024-01-03 18:30:24');
In the above screenshot, the extract function only returns a single-digit number. To return a day as a double digit, use the TO_CHAR function as shown below.
SELECT TO_CHAR(TIMESTAMP '2024-01-03 18:30:24','DD');
Day of the Week in Number (0–Sunday)
Use DOW to extract the day of the week, as shown in the below example. PostgreSQL returns a numeric field between 0 and 6. It uses 0 as Sunday and Saturday as a number 6.
SELECT EXTRACT(dow from TIMESTAMP '2024-01-19 21:30:24');
Day Name (Sunday/Monday)
To show the day name from the timestamp field, choose the TO_CHAR function. Use the lowercase letter “day” to show the day name in lowercase letters, or use the capital letter “DAY” to show the day name in capital letters, as shown in the below example.
SELECT
TO_CHAR(TIMESTAMP '2024-05-03 21:30:04', 'day'),
TO_CHAR(TIMESTAMP '2024-05-03 21:30:04', 'DAY')
Day of the Year (1-365/366)
Use “doy” to show the day of the year in the timestamp field. It returns values from 1 to 366. The 366 number is used for the leap year.
SELECT EXTRACT(doy from TIMESTAMP '2024-05-19 21:30:24');
Extract Month from timestamp
Get Month Number (1–12)
To extract month number, use month as field name in extract function of PostgreSQL
SELECT EXTRACT(month FROM TIMESTAMP '2024-01-03 18:30:24');
Use the TO_CHAR function if you need to use a two-digit month number, as shown below:
SELECT TO_CHAR(TIMESTAMP '2024-01-03 18:30:24', 'mm');
Get Month Name (January/jan)
Use the TO_CHAR function to get the month name from the timestamp value. Choose the field name “month” if you need a full month name, or use “mon” if you need a three-character month name.
SELECT TO_CHAR(TIMESTAMP '2024-01-03 18:30:24', 'month'), TO_CHAR(TIMESTAMP '2024-01-03 18:30:24', 'mon');
If you need the first letter capital in the month name, choose “Month,” or if you need the full month in capital letters, then choose the field name as “MONTH.”.
SELECT TO_CHAR(TIMESTAMP '2024-01-03 18:30:24', 'Month'), TO_CHAR(TIMESTAMP '2024-01-03 18:30:24', 'MONTH');
Extract Year from timestamp
To extract a year, put the field name “year” in the field name of the extract function, as shown below.
SELECT EXTRACT(year from TIMESTAMP '2024-01-03 18:30:24')
If you need to extract two digits from the year, then choose the TO_CHAR function as shown below.
SELECT TO_CHAR(TIMESTAMP '2024-01-03 18:30:24', 'yy');
Extract Hour from timestamp
24-hour format
Use “hour” as the field name in the extract function to get hours from a timestamp.
SELECT EXTRACT(hour from TIMESTAMP '2024-01-03 05:30:24');
To get an hour in two digits, use the TO_CHAR function and “hh” as the field name.
SELECT TO_CHAR(TIMESTAMP '2024-01-03 05:30:24', 'hh');
12-hour format
The above query returns the hour in 24-hour format. To extract hours in 12-hour format, use hh12, as shown in the below example.
SELECT
TO_CHAR(TIMESTAMP '2024-01-03 21:30:24', 'hh12'),
TO_CHAR(TIMESTAMP '2024-01-03 21:30:24', 'hh24');
AM/PM format
To extract hours in AM/PM format, use hh A.M. as shown below.
SELECT
TO_CHAR(TIMESTAMP '2024-01-03 21:30:24', 'hh A.M.'),
TO_CHAR(TIMESTAMP '2024-01-03 21:30:24', 'hh a.m.');
Extract Minutes from timestamp
Use “minute” as a field name in the Extract function.
SELECT EXTRACT(minute from TIMESTAMP '2024-01-03 21:30:24');
Extract Seconds from timestamp
Use second as a field name in the extract function to get seconds from a timestamp. The extract function returns seconds in decimal format. The number after the decimal format is for milliseconds.
To extract only the second field in two-digit format, use the TO_CHAR function as shown below.
SELECT
TO_CHAR(TIMESTAMP '2024-01-03 21:30:04', 'ss')
Summary
Use the Extract function to get the day, month, year, hour, minute, or second from the timestamp column. If you need more control over formatting, use the TO_CHAR function.
Related Posts