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.
This exercise is part of the course
Exploratory Data Analysis in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Count requests created on January 31, 2017
SELECT count(*)
FROM evanston311
WHERE ___;