Great Postgres Date Functions To Know

Below the video are great date functions on Postgres. However, you can first witness a two-minute video that joins a Postgres table with tables from Databricks, Snowflake, Redshift, Google BiqQuery, Synapse, Vertica, Teradata, Oracle, SQL Server, DB2, Yellowbrick, MySQL, Microsoft Access, Excel in a single federated query.

You can now download our books on Synapse, Redshift, Google BigQuery, DB2, Oracle, Yellowbrick, Vertica, MySQL, Postgres, and more for FREE.

You can download the PDFs from our CoffingDW.com website immediately.

Here is how you do it. Go to CoffingDW.com and click on Books from the top right menu. You will then be in the CoffingDW bookstore, where you will see the Snowflake book. Add the Snowflake book to your cart.

Add any book you want to your cart.

When you check out, you use the coupon FRIEND2023.

You won’t have to enter any credit card information. Just put in your name and email, and the price will be discounted to zero. You can then download the book immediately at no cost to you.

Below are the keywords to get the date, time, or timestamp. These are reserved words that the system will deliver to you when requested.

Transaction_Timestamp is equivalent to CURRENT_TIMESTAMP but is named to reflect what it returns. For example, statement_Timestamp returns the start time of the current statement. Clock_Timestamp returns the current time, and values can change within a single SQL command.

NOW is a traditional PostgreSQL equivalent to transaction_timestamp(). The Timeofday function returns the current time as a formatted text string rather than a timestamp with a time zone.

Adding or subtracting a number from a date adds or subtracts several days from the date.

When subtracting between two dates, you get the approximate number of days between those dates.

You can use the To_Char function to format dates.

The below examples show how robust the EXTRACT command is at extracting portions of a date, time, or timestamp.

The below examples use the Date_Part function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes.

The below examples use the Date_Trunc function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes. Date_Trunc selects which precision to truncate the input value. The return value is of type timestamp or interval. The valid text The above examples use the Date_Trunc function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes. Date_Trunc selects which precision to truncate the input value. The return value is of type timestamp or interval.

The first example determines the age between two timestamps and the result that uses years, months, and days, and it is of the type INTERVAL. The second example subtracts the single timestamp from the current_date at midnight.

Intervals add or subtract a period from a date at specific intervals. Notice that the interval is aware of the leap year.