Skip to main content
HomeAbout SQLLearn SQL

Hacking Date Functions in SQLite

In this tutorial, learn how to use date functions in SQLite.
Oct 2018  · 3 min read

Some of the most useful functions in Postgres implementations of SQL (like Amazon Redshift)are DATE_DIFF and DATE_TRUNC:

  • DATE_DIFF gives the amount of time that has elapsed between two different dates. For example, the following code would give the number of days between date1 and date2:
DATE_DIFF('day', date1, date2)

DATE_DIFF is great for calculating the number of days from sign-up to cancellation or the number of hours from sign-in to sign-out.

  • DATE_TRUNC cuts off a date to the nearest day, week, month, or year. For example, the following code would give the nearest Monday, to the timestamp my_timestamp:
DATE_TRUNC('week', my_timestamp)

DATE_TRUNC is great for aggregating data. For instance, we can use it to find the number of Monthly Active Users (MAU) by truncating to the nearest month start date.

But not every SQL implementation had these great functions. For our Learn SQL from Scratch code, we use SQLite, a light-weight implementation of SQL that can be run on a single Docker instance. SQLite is great for website backends and small projects, but it's missing my two favorite functions. Luckily, there are workarounds.

To emulate, DATE_DIFF, we can use a little-known function called juliandate. According to Wikipedia, ​"the ​Julian Day Number​ (​JDN​) is the integer assigned to a whole solar day in the Julian day count starting from noon ​Universal time​, with Julian day number 0 assigned to the day starting at noon on Monday, January 1, ​4713 BC​". By converting a date to a floating point number, we can use subtraction to find the difference between two timestamps.

julian day number

We can even convert our answer to hours by multiplying by 24 or to minutes by multiplying by 24 * 60.

We can emulate some of the functionality of DATE_TRUNC by using strftime. This function converts a timestamp to a string with a given output.

%dday of month: 00 %ffractional seconds: SS.SSS %Hhour: 00-24%jday of year: 001-366 %JJulian day number%mmonth: 01-12 %Mminute: 00-59 %sseconds since 1970-01-01 %Sseconds: 00-59 %wday of week 0-6 with Sunday==0 %Wweek of year: 00-53 %Yyear: 0000-9999

Normally, we might use this to convert between different timestamp formats like YYYY-MM-DD to MM-DD-YYYY:

strftime('%M-%D-%Y', mydate)

But we can cleverly choose our formatting to truncate to the appropriate point. For example, if we want to truncate to the nearest month, we can:

strftime('%M/%Y', mydate)

Or we can truncate to the nearest week, by using:

strftime('%Y-%w', mydate)

With these two easy tricks, you can use SQLite for some of the same great analyses as Amazon Redshift!

If you would like to learn more about the basics of SQL, take DataCamp's Intro to SQL for Data Science course and check out our SQL Tutorial for Beginners.

Learn more about SQL

Introduction to SQL

Beginner
2 hr
265.8K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

A duck codes in DuckDB

DuckDB makes SQL a first-class citizen on DataCamp Workspace

In this blog post, we list out all recent improvements that make it seamless and efficient to query data with SQL, all without leaving the tool.
Filip Schouwenaars's photo

Filip Schouwenaars

The 80 Top SQL Interview Questions and Answers for Beginners & Intermediate Practitioners

This article provides a comprehensive overview of 80 essential SQL questions and answers for job hunters, hiring managers, and recruiters, covering both general topics and technical questions.
Elena Kosourova's photo

Elena Kosourova

33 min

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
Richie Cotton's photo

Richie Cotton

10 min

How to Use SQL in pandas Using pandasql Queries

Unleash the power of SQL within pandas and learn when and how to use SQL queries in pandas using the pandasql library for seamless integration.
Elena Kosourova's photo

Elena Kosourova

8 min

How to Practice SQL Using any Dataset with Workspace

Learn how DataCamp Workspace optimizes the experience of working with Jupyter notebooks and SQL. Discover how to effortlessly write SQL queries, connect to databases, analyze CSV files, and leverage the power of AI assistance
Richie Cotton's photo

Richie Cotton

9 min

See MoreSee More