1. Learn
    2. /
  2. Courses
    3. /
  3. Exploratory Data Analysis in SQL

Exercise

Date comparisons

When working with timestamps, sometimes you want to find all observations on a given day. However, if you specify only a date in a comparison, you may get unexpected results. This query:

SELECT count(*) 
  FROM evanston311
 WHERE date_created = '2018-01-02';

returns 0, even though there were 49 requests on January 2, 2018.

This is because dates are automatically converted to timestamps when compared to a timestamp. The time fields are all set to zero:

SELECT '2018-01-02'::timestamp;

 2018-01-02 00:00:00

When working with both timestamps and dates, you'll need to keep this in mind.

Instructions 1/3

undefined XP
  • 1
    • Count the number of Evanston 311 requests created on January 31, 2017 by casting date_created to a date.
  • 2
    • Count the number of Evanston 311 requests created on February 29, 2016 by using >= and < operators.
  • 3
    • Count the number of requests created on March 13, 2017.
    • Specify the upper bound by adding 1 to the lower bound.