PostgreSQL – EXTRACT Function

ByAdmin

In PostgreSQL, the EXTRACT function is used to extract specific components (such as year, month, day, hour, etc.) from date and time values. This function allows you to retrieve individual parts of a timestamp or interval.

The basic syntax of the EXTRACT function is as follows: 

EXTRACT(field FROM source);

where field is the part of the date or time that you want to extract (e.g., year, month, day, hour, minute, second);

and source is the date, time, or interval from which you want to extract the specified field.

Below are some examples of using the EXTRACT function:

  1. Extracting the year from a date:
SELECT EXTRACT(YEAR FROM '2023-08-06') AS year;

This query will return the year 2023.

  1. Extracting the month from a timestamp:
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-08-06 15:30:45') AS month;

This query will return the month 8.

  1. Extracting the day from an interval:
SELECT EXTRACT(DAY FROM INTERVAL '5 days') AS days;

This query will return the number of days 5.

  1. Extracting the hour from the current timestamp:
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour;

This query will return the current hour.

You can use various date and time fields with the EXTRACT function, such as:

  • YEAR, MONTH, DAY, HOUR, MINUTE, SECOND: To extract the corresponding components from the date or timestamp.
  • TIMEZONE: To extract the time zone from the timestamp.
  • EPOCH: To extract the number of seconds since January 1, 1970 (UNIX epoch time).

Here’s an example of extracting the time zone and epoch from a timestamp: 

SELECT EXTRACT(TIMEZONE FROM TIMESTAMP '2023-08-06 15:30:45') AS timezone, EXTRACT(EPOCH FROM TIMESTAMP '2023-08-06 15:30:45') AS epoch;

EXTRACT function returns numeric values for most components, but it returns text values for certain components, such as the time zone (e.g., 'UTC', 'America/New_York', etc.).

The EXTRACT function is useful when you need to work with specific date and time components in your PostgreSQL queries or perform calculations based on these components

Similar Posts

PostgreSQL – TRANSACTION_TIMESTAMP

ByAdmin

In PostgreSQL, transaction_timestamp() is a built-in function that returns the current timestamp (including both date and time) of the start of the current transaction. This function is often used in SQL queries and transactions to record the timestamp when a particular operation or transaction began. Here’s how you can use transaction_timestamp(): SELECT transaction_timestamp(); The result…

PostgreSQL – CURRENT_TIMESTAMP Function

ByAdmin

In PostgreSQL, the CURRENT_TIMESTAMP function is used to retrieve the current date and time along with the time zone information. It returns the current timestamp in the timestamp with time zone data type, which includes the date, time, seconds, milliseconds, and the time zone offset. The basic syntax of the CURRENT_TIMESTAMP function is as follows:…

PostgreSQL – AGE Function

ByAdmin

In PostgreSQL, the AGE() function is used to calculate the difference between two dates or timestamps and returns the result as an interval. The interval represents the duration between the two dates in years, months, days, and fractional seconds. The basic syntax of the AGE() function is as shown below: AGE(end_date, start_date); where end_date is…

PostgreSQL – LOCALTIMESTAMP

ByAdmin

The localtimestamp function is used to retrieve the current timestamp according to the time zone setting of your database session. This function returns the current date and time as a timestamp with time zone. Here’s how you can use localtimestamp in PostgreSQL: SELECT localtimestamp; The above SQL statement will return the current timestamp based on…