import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://bob:1234@localhost:5432/dvdrental')
engine.execution_options(isolation_level="AUTOCOMMIT")
%load_ext sql
%sql $engine.url

'Connected: bob@dvdrental'

Time/Date functions: Extract

Postgres has support for the full set of SQL Date and Time types. The EXTRACT function allows us to extract parts from a date (year, month, day of the month or of the week, hour, minutes, etc). The general syntax for using this function would be:

SELECT extract(day from column_with_date)
FROM table_1;

That would return the day of the month of the column_with_date for the first 10 rows. Instead of day we could have also used any of the time units mentioned at the beginning. Let’s see an example:

%%sql
SELECT
    extract(day from payment_date) AS day,
    extract(month from payment_date) AS month,
    extract(year from payment_date) AS year
FROM payment
LIMIT 10;

 * postgresql://bob:***@localhost:5432/dvdrental
10 rows affected.

day	month	year
15	2	2007
16	2	2007
16	2	2007
19	2	2007
20	2	2007
21	2	2007
17	2	2007
20	2	2007
20	2	2007
16	2	2007

Now let’s say we’re interested in knowing about the total amount of the payments per month:

%%sql
SELECT sum(amount), extract(month from payment_date) AS month
FROM payment
GROUP BY month;

 * postgresql://bob:***@localhost:5432/dvdrental
4 rows affected.

sum	month
8351.84	2
23886.56	3
514.18	5
28559.46	4