Reference document for running time range queries in SQL

Strategies:

  1. Using Time Interval
  2. Using Start and/or End Dates
  3. Using EXTRACT()
  4. Using DATE_PART
SELECT *
FROM <table>
WHERE reference_date >= now() - INTERVAL '30 days'

INTERVAL might be used with days, seconds, hours, etc.

INTERVAL '1000 seconds'
INTERVAL '30 minutes'
INTERVAL '72 hours'
INTERVAL '14 days'
INTERVAL '3 weeks'
INTERVAL '2 months'
INTERVAL '1 year'

Reference: PostgresSQL Date Time Functions and Operators

Try filtering where reference_date is greater than a specific date:

SELECT *
FROM <table>
WHERE reference_date > date '2001-09-28'

Try filtering where reference_date is between specific dates:

SELECT *
FROM <table>
WHERE reference_date > date '2001-09-28'
AND reference_date < date '2001-10-28'

Reference: PostgresSQL Date Time Functions and Operators

You can extract day, month, or year from a date. Very handy.

SELECT *
FROM <table>
WHERE EXTRACT(month from reference_date) = 2
and EXTRACT(year from reference_date) = 2020

You can also try selecting and grouping with EXTRACT():

SELECT
EXTRACT(month from reference_date) as month_extract
sum(some_int_column)
FROM <table>
WHERE EXTRACT(month from reference_date) < 3
and EXTRACT(year from reference_date) = 2020
GROUP BY month_extract

Reference: StackOverflow.com and PostgresSQL Date Time Functions and Operators

Imagine you want to extract an age value from a “date_of_birth” date. Two examples: (1) static date and (2) today’s date:

# USING A STATIC DATESELECT
name,
date_of_birth,
DATE_PART('year', date '2020-03-05') - DATE_PART('year', date_of_birth) as age
FROM table
# USING TODAY'S DATESELECT
name,
date_of_birth,
DATE_PART('year', now()) - DATE_PART('year', date_of_birth) as age
FROM table

Written by

Documentation and tutorials on Python, Programming, and Data Analysis. FPL Addict. Occasionally writing about biohacking, PMing, and food.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store