Dates, Timestamps, and Intervals - Examples

Select the Last Day of the Month:

SELECT (DATE_TRUNC('MONTH', ('201608' || '01')::DATE) + INTERVAL '1 MONTH - 1 day')::DATE;

This statement selects the last day of the month for August 2016.

Cast a Timestamp or Interval to a String:

• Convert Timestamp to String:

SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'DD Mon YYYY HH:MI:SSPM');

Output: "12 Aug 2016 04:40:32PM"

• Customized Format and Text Insertion:

SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, '"Today is "FMDay", the "DDth" day of the month of "FMMonth" of "YYYY');

Output: "Today is Saturday, the 12th day of the month of August of 2016"

• Localized String:

SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'TMDay, DD" de "TMMonth" del año "YYYY');

Output (with Spanish locale): "Sábado, 12 de Agosto del año 2016"

Count the Number of Records per Week:

SELECT DATE_TRUNC('week', your_date_column) AS "Week", COUNT(*)
FROM your_table
GROUP BY 1
ORDER BY 1;

This statement counts the number of records per week based on the specified date column in your table.

These examples illustrate how to manipulate dates, timestamps, and intervals in PostgreSQL, including selecting the last day of the month, casting to strings, and counting records per week.