Processing Function and Data Sources

Explore how to apply PostgreSQL's rich set of processing functions for date and time data within SQL queries. Understand the use of the from clause for accessing and joining multiple data sources. Practice with examples that demonstrate complex date computations and combining data from different tables.

We'll cover the following...

Functions for date and time
The from clause
Try it yourself

PostgreSQL embeds a very rich set of processing functions that can be used anywhere in the queries, even if most of them are more useful in the select clause. Because we see a lot of code fetching only the raw data from the RDBMS and then doing all the processing in the application code, we want to show an example query processing calendar-related information with PostgreSQL.

Functions for date and time

The following query is a showcase for extract() and to_char() functions, and it also uses the case construct. Read the documentation on date/time functions and operators for more details and functions on the same topic.

PostgreSQL

select date::date,
       extract('isodow' from date) as dow,
       to_char(date, 'dy') as day,
       extract('isoyear' from date) as "iso year",
       extract('week' from date) as week,
       extract('day' from
               (date + interval '2 month - 1 day')
              )
        as feb,
       extract('year' from date) as year,
       extract('day' from
               (date + interval '2 month - 1 day')
              ) = 29
       as leap
  from generate_series(date '2000-01-01',
                       date '2010-01-01',
                       interval '1 year')
       as t(date);