SQLite Date Functions: A Comprehensive Guide

Understanding date functions in SQLite is key to effectively working with timestamps in your data. Whether you need to extract parts of a date, format dates, calculate durations, or manipulate dates in other ways, SQLite has you covered with a versatile set of functions.

In this guide, we’ll explore the most essential date functions in SQLite using real-world examples with sample analytics data. We’ll cover how to create dates, extract date parts, format dates, calculate durations between dates and even handle time zones.

To follow along, you’ll want to have SQLite installed. Let’s dive in!

SQLite offers versatile date functions to manipulate timestamps and calculate with dates and times. Key functions allow you to format dates, extract date parts like day or year, calculate durations between dates, convert between timezones and more. Using functions like date(), time(), datetime(), strftime()and julianday(), you can effectively work with dates and timestamps in SQLite.

Creating a Sample Table with Timestamps

To demonstrate the date functions, we’ll use a sample analytics table that tracks website sessions with start and end timestamps:

CREATE TABLE website_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  session_id VARCHAR(50),
  user_id INTEGER,  
  device VARCHAR(50),
  browser VARCHAR(50),  
  os VARCHAR(50), 
  landing_page VARCHAR(100),
  exit_page VARCHAR(100),
  session_start DATETIME, 
  session_end DATETIME
);

Let’s insert some sample data:

INSERT INTO website_sessions
(session_id, user_id, device, browser, os, landing_page, exit_page, session_start, session_end)
VALUES
("00001", 10001, "Desktop", "Chrome", "Windows", "/home", "/contact", "2023-01-01 09:23:00", "2023-01-01 09:35:00"); 

This gives us a table to now demonstrate SQLite’s handy date functions.

Extracting Date Parts

You can extract parts of a date/time like day, month, year etc. using these functions:

strftime()

The most flexible way to extract date parts is strftime():

SELECT 
  session_id,
  strftime('%Y', session_start) AS year,
  strftime('%m', session_start) AS month,
  strftime('%d', session_start) AS day
FROM website_sessions;

Output:

session_id    year    month   day
00001         2023    01      01

You specify the date part to extract using format specifiers like %Y for 4-digit year, %m for zero-padded month and %d for 0-padded day.

This makes strftime() versatile to extract any date part you want.

Date and Time Functions

SQLite also provides specific functions to extract parts of a date/time:

SELECT
  session_id,
  DATE(session_start) AS date,
  TIME(session_start) AS time,
  YEAR(session_start) AS year, 
  MONTH(session_start) AS month,
  DAY(session_start) AS day
FROM website_sessions;  

Output:

session_id   date       time        year   month   day
00001        2023-01-01  09:23:00    2023   1       1

So with DATE() you extract just the date part, with TIME() just the time part.

You can also directly extract the year, month or day with YEAR(), MONTH() and DAY().

Formatting Dates

To format dates and times in SQLite, use the strftime() function:

SELECT
  session_id, 
  strftime('%Y-%m-%d %H:%M:%S', session_start) AS formatted_date
FROM website_sessions;

Output:

session_id   formatted_date
00001        2023-01-01 09:23:00  

Here we format the date as YYYY-MM-DD HH:MM:SS using the format specifiers.

Some other examples of formatting timestamps:

-- Format date as January 1st, 2023
SELECT strftime('%B %dst, %Y', session_start) FROM website_sessions;

-- Format time as 09:23 AM  
SELECT strftime('%I:%M %p', session_start) FROM website_sessions; 

This makes strftime() very versatile for formatting dates and times!

Calculating Durations Between Dates

To calculate the duration between two dates/times, just subtract them:

SELECT 
  session_id,
  session_end - session_start AS duration
FROM website_sessions;

Output:

session_id   duration
00001        00:12:00

The result is an interval value, here showing 12 minutes duration.

You can also extract parts of the interval to get the duration in seconds, minutes etc:

SELECT
  session_id,
  CAST((julianday(session_end) - julianday(session_start)) * 86400 AS INTEGER) AS duration_sec  
FROM website_sessions;

Output:

session_id   duration_sec
00001         720

Here we calculated the duration in seconds using Julian day numbers to make the duration calculation easier.

There are many creative ways to calculate durations between dates/times in SQLite.

Comparing Dates

SQLite allows comparing dates and times just like numbers:

SELECT * 
FROM website_sessions
WHERE session_start > '2023-01-01';

This finds sessions after January 1st 2023.

Some more examples:

-- Sessions on January 1st  
SELECT * FROM website_sessions
WHERE DATE(session_start) = '2023-01-01';

-- Sessions longer than 10 minutes
SELECT * FROM website_sessions  
WHERE session_end - session_start > '00:10:00';   

So you can filter, constrain and sort by dates just like numbers or strings. Very handy!

Date Manipulation Functions

For manipulating dates, you can:

• Add/substract days, hours etc.
• Extract date parts
• Construct new dates

Functions to manipulate dates:

• DATE() – Extract date part
• TIME() – Extract time part
• DATETIME() – Construct a date/time
• JULIANDAY() – Get Julian day number
• strftime() – Format dates

SELECT
  DATE(session_start) AS original_date,
  DATE(session_start, '+7 days') AS week_later,
  TIME(session_start) AS original_time,  
  DATETIME(DATE(session_start), TIME(session_start, '-30 minutes')) AS thirty_mins_earlier  
FROM website_sessions;

Output:

original_date  week_later    original_time      thirty_mins_earlier
2023-01-01     2023-01-08    09:23:00           2023-01-01 09:08:00  

Here we:

• Extracted the original date and time parts
• Added 7 days to the date
• Subtracted 30 minutes from the time
• Constructed a new datetime

So you can manipulate dates and times in very flexible ways.

Handling Time Zones

SQLite does not inherently store time zone information with dates and times.

But you can use Unix timestamps and functions for time zone conversions:

SELECT 
  session_id,
  datetime(session_start, 'unixepoch', 'localtime') AS local_time,
  datetime(session_start, 'unixepoch', 'utc') AS utc_time  
FROM website_sessions;

Output

session_id  local_time               utc_time
00001       2023-01-01 09:23:00      2023-01-01 14:23:00

Here we take the UTC timestamp and convert it to local time and UTC representations.

SQLite cannot represent or store timestamps in different timezones – everything converts back to UTC timestamps behind the scenes. But conversions on retrieval let you work with local times.

Summary of SQLite Date Functions

This covers the key date functions available in SQLite.

With these versatile functions, you can format, manipulate, extract, compare and calculate with dates and times in very powerful ways. Date handling is a vital part of working with temporal data in analytics databases and SQLite offers all the tools you need.