PostgreSQL – DATE_PART Function

ByAdmin

In PostgreSQL, the DATE_PART function is used to extract a specific element (e.g., year, month, day, hour, minute, second, etc.) from a date, time, or timestamp value. It returns the numeric value of the specified component.

The basic syntax of the DATE_PART function is as shown below: 

DATE_PART(field, source);

where field is the part of the date or time you want to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.). The field parameter is case-insensitive, so you can use either uppercase or lowercase letters;

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

Below are some examples of using the DATE_PART function:

  1. Extracting the year from a date:
SELECT DATE_PART('year', '2023-08-06') AS year;

This query will return the year 2023.

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

This query will return the month 8.

  1. Extracting the day from an interval:
SELECT DATE_PART('day', INTERVAL '5 days') AS days;

This query will return the number of days 5.

  1. Extracting the hour from the current timestamp:
SELECT DATE_PART('hour', CURRENT_TIMESTAMP) AS hour;

This query will return the current hour.

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

  • 'year', 'month', 'day', 'hour', 'minute', 'second': To extract the corresponding elements 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 DATE_PART('timezone', TIMESTAMP '2023-08-06 15:30:45') AS timezone, DATE_PART('epoch', TIMESTAMP '2023-08-06 15:30:45') AS epoch;

The DATE_PART 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 DATE_PART function is useful when you need to extract specific date and time components in your PostgreSQL queries or perform calculations based on these components.

Similar Posts

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 – STATEMENT_TIMESTAMP

ByAdmin

The statement_timestamp() function is used to retrieve the timestamp when the current statement within a transaction started. Unlike transaction_timestamp(), which gives you the timestamp when the transaction started (and remains constant throughout the transaction), statement_timestamp() provides the timestamp specific to the current SQL statement being executed. This can be useful for recording when individual statements…

PostgreSQL -CLOCK_TIMESTAMP

ByAdmin

The clock_timestamp() function is used to retrieve the current timestamp as of the current transaction’s start time. It returns a timestamp with time zone that represents the exact time when the current transaction began. Here’s how you can use the clock_timestamp() function in PostgreSQL: SELECT clock_timestamp(); The above SQL statement will return the current timestamp,…

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…