Date/time components and aggregation

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

Common date/time fields

Date/Time Functions and Operators Documentation

Fields

century: 2019-01-01 = century 21
decade: 2019-01-01 = decade 201
year, month, day
hour, minute, second
week
dow: day of week

Extracting fields

-- functions to extract datetime fields

date_part('field', timestamp)

EXTRACT(FIELD FROM timestamp)

-- now is 2019-01-08 22:15:10.647281-06

SELECT date_part('month', now()),
       EXTRACT(MONTH FROM now());

 date_part | date_part 
-----------+-----------
         1 |         1

Extract to summarize by field

Individual sales

SELECT * 
  FROM sales
 WHERE date >= '2010-01-01' 
   AND date < '2017-01-01';

    date    | amt
------------+------
 2010-01-05 |  32
 2010-03-04 |  10
 2010-07-29 |   7
 2016-12-20 |  46
 2015-11-01 |  30
 2014-07-10 |  35
 2013-09-09 |  15
 2011-04-22 |   9
...

By month

SELECT date_part('month', date) 
         AS month,
       sum(amt)
  FROM sales
 GROUP BY month
 ORDER BY month;

 month     sum
-------+--------
     1 |   432     <- Jan
     2 |   847
     3 |  1987     <- Mar
     4 |  3899
     5 |   974
     6 |   397
     7 |   974
     8 |   198
...

Truncating dates

date_trunc('field', timestamp)

-- now() is  2018-12-17 21:45:15.6829-06

SELECT date_trunc('month', now());

       date_trunc       
------------------------
 2018-12-01 00:00:00-06

Truncate to keep larger units

Individual sales

SELECT * 
  FROM sales
 WHERE date >= '2017-06-01' 
   AND date < '2019-02-01';

        date         | amt 
---------------------+-----
 2019-01-25 06:58:32 |  10
 2018-03-14 18:07:03 |  11
 2018-04-15 19:03:33 |   5
 2018-08-10 14:30:49 |  28
 2018-02-02 09:52:28 |  17
 2017-08-08 08:40:35 |  20
 2018-07-05 02:05:52 |   7
 2018-07-28 17:49:16 |  16
 2018-08-01 20:57:40 |   8
 2018-03-04 08:56:04 |  32
 ...

By month with year

SELECT date_trunc('month', date)
         AS month
       sum(amt)
  FROM sales
 GROUP BY month
 ORDER BY month;

        month        |  sum 
---------------------+------
 2017-06-01 00:00:00 |  594 
 2017-07-01 00:00:00 | 3824 
 2017-08-01 00:00:00 |  482
 2017-09-01 00:00:00 | 1384
 2017-10-01 00:00:00 | 3058
 2017-11-01 00:00:00 |  259
 2017-12-01 00:00:00 |  874   
 2018-01-01 00:00:00 | 1225
...

Time to practice extracting and aggregating dates

Exploratory Data Analysis in SQL