PostgreSQL – DATE_TRUNC Function

ByAdmin August 9, 2023August 9, 2023

In PostgreSQL, the DATE_TRUNC function is used to truncate a date, time, or timestamp value to a specified precision. Truncation means setting specific parts of the date or time to zero or a default value while keeping the more significant parts unchanged.

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

DATE_TRUNC(precision, source);

where precision is the precision to which you want to truncate the date or time (e.g., ‘year’, ‘quarter’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.). The precision parameter is case-insensitive.

and source is the date, time, or timestamp that you want to truncate.

Here are some examples of using the DATE_TRUNC function:

Truncating a timestamp to the beginning of the month:

SELECT DATE_TRUNC('month', TIMESTAMP '2023-08-06 15:30:45') AS truncated_timestamp;

This query will return 2023-08-01 00:00:00, effectively setting the day and time components to the beginning of the month.

Truncating a date to the beginning of the year:

SELECT DATE_TRUNC('year', '2023-08-06') AS truncated_date;

This query will return 2023-01-01, setting the month and day components to the beginning of the year.

Truncating a timestamp to the beginning of the hour:

SELECT DATE_TRUNC('hour', TIMESTAMP '2023-08-06 15:30:45') AS truncated_timestamp;

This query will return 2023-08-06 15:00:00, setting the minutes and seconds components to zero.

The DATE_TRUNC function is useful when you need to aggregate or group data by specific time periods (e.g., months, years) or perform calculations based on a particular time resolution. It allows you to normalize timestamps and dates, making them easier to work with.

The result of the DATE_TRUNC function will have the same data type as the source value. If the source is a timestamp, the truncated value will be a timestamp as well. Similarly, if the source is a date, the truncated value will be a date. The time zone information is retained when using DATE_TRUNC with timestamps.

Date Functions

PostgreSQL – NOW Function

ByAdmin August 8, 2023August 8, 2023

In PostgreSQL, the NOW() function is used to fetch the current date and time according to the server’s clock. It returns the current timestamp in the timestamp with time zone format. The NOW() function is commonly used when you want to record the current date and time in a database table or perform date and…

Date Functions

PostgreSQL – CURRENT_TIME Function

ByAdmin August 8, 2023August 8, 2023

In PostgreSQL, you can use the CURRENT_TIME function to retrieve the current time without the date component. It returns the current time in the time data type, which includes the hours, minutes, seconds, and fractional seconds. The basic syntax of the CURRENT_TIME function is as shown below: SELECT CURRENT_TIME; Below is an example of using…

Date Functions

PostgreSQL – TO_DATE Function

ByAdmin August 8, 2023August 8, 2023

In PostgreSQL, the TO_DATE function is used to convert a string representation of a date into a date data type. It is often used to parse date strings in a specific format and convert them into valid date values that can be stored or used for date-related operations. The basic syntax of the TO_DATE function…

Date Functions

PostgreSQL – EXTRACT Function

ByAdmin August 9, 2023August 9, 2023

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…

Date Functions

PostgreSQL – STATEMENT_TIMESTAMP

ByAdmin September 21, 2023September 21, 2023

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…

Date Functions

PostgreSQL -CLOCK_TIMESTAMP

ByAdmin September 21, 2023September 21, 2023

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,…

Related

Similar Posts