You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/


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.

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.

Posted in Blog