beginner 6 min read

Working with Dates and Times

Written with a little help from AI, reviewed and edited by a human.

When building applications, you often need to work with dates and times — like storing when a user registered, finding posts from the last 7 days, or calculating someone’s age. SQL has built-in functions to handle dates and times, but they vary quite a bit between different database systems.

Real life example

Imagine you’re managing a library. You need to:

  • Record when a book was checked out
  • Find books that are overdue (checked out more than 14 days ago)
  • Calculate how many days a book has been on loan
  • Show only books checked out this month

Date and time functions let you do all of this in your SQL queries.

Storing dates

First, you need to understand the date/time types:

  • DATE — Just the date: 2026-02-22
  • TIME — Just the time: 14:30:00
  • DATETIME or TIMESTAMP — Both date and time: 2026-02-22 14:30:00

When creating a table:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title VARCHAR(255),
  created_at DATETIME,
  published_date DATE
);

Getting the current date/time

Different databases use different functions for this:

SQLite:

SELECT DATE('now');        -- Current date: 2026-02-22
SELECT TIME('now');        -- Current time: 14:30:00
SELECT DATETIME('now');    -- Current date and time

PostgreSQL:

SELECT CURRENT_DATE;       -- Current date
SELECT CURRENT_TIME;       -- Current time
SELECT NOW();              -- Current timestamp

MySQL:

SELECT CURDATE();          -- Current date
SELECT CURTIME();          -- Current time
SELECT NOW();              -- Current timestamp

Let’s use SQLite syntax for the rest of this post, but the concepts apply everywhere.

Inserting dates

You can insert the current timestamp:

INSERT INTO posts (title, created_at)
VALUES ('My first post', DATETIME('now'));

Or a specific date:

INSERT INTO posts (title, published_date)
VALUES ('Hello world', '2026-02-22');

Dates should be in the format YYYY-MM-DD and timestamps in YYYY-MM-DD HH:MM:SS.

Extracting parts of a date

You can extract the year, month, day, etc. from a date:

SQLite:

SELECT
  created_at,
  DATE(created_at) AS date_only,
  TIME(created_at) AS time_only,
  strftime('%Y', created_at) AS year,
  strftime('%m', created_at) AS month,
  strftime('%d', created_at) AS day,
  strftime('%H', created_at) AS hour
FROM posts;

strftime (string format time) is SQLite’s way of extracting and formatting dates. The %Y, %m, %d are format codes:

  • %Y — 4-digit year (2026)
  • %m — Month (01-12)
  • %d — Day (01-31)
  • %H — Hour (00-23)
  • %M — Minute (00-59)
  • %S — Second (00-59)

PostgreSQL/MySQL use EXTRACT or YEAR(), MONTH(), DAY() functions:

SELECT
  YEAR(created_at) AS year,
  MONTH(created_at) AS month,
  DAY(created_at) AS day
FROM posts;

Finding posts from a specific time period

Find posts from the last 7 days:

SELECT * FROM posts
WHERE created_at >= DATE('now', '-7 days');

DATE('now', '-7 days') means “today’s date minus 7 days”.

Find posts from this month:

SELECT * FROM posts
WHERE strftime('%Y-%m', created_at) = strftime('%Y-%m', 'now');

This extracts the year and month (2026-02) from both the post date and today’s date and compares them.

Date arithmetic

You can add or subtract time from dates:

SQLite:

SELECT DATE('now', '+7 days');     -- 7 days from now
SELECT DATE('now', '-1 month');    -- 1 month ago
SELECT DATE('now', '+1 year');     -- 1 year from now

PostgreSQL:

SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';

MySQL:

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

Calculating differences between dates

Find how many days between two dates:

SQLite:

SELECT JULIANDAY('2026-03-01') - JULIANDAY('2026-02-22') AS days_diff;

Result: 7 (7 days between Feb 22 and Mar 1)

PostgreSQL:

SELECT AGE('2026-03-01', '2026-02-22');

MySQL:

SELECT DATEDIFF('2026-03-01', '2026-02-22');

Formatting dates for display

SQLite uses strftime:

SELECT strftime('%d/%m/%Y', created_at) AS formatted_date
FROM posts;

For 2026-02-22, this returns 22/02/2026.

PostgreSQL uses TO_CHAR:

SELECT TO_CHAR(created_at, 'DD/MM/YYYY') FROM posts;

MySQL uses DATE_FORMAT:

SELECT DATE_FORMAT(created_at, '%d/%m/%Y') FROM posts;

Sorting by date

Order posts from newest to oldest:

SELECT * FROM posts
ORDER BY created_at DESC;

Or oldest to newest:

SELECT * FROM posts
ORDER BY created_at ASC;

Grouping by date

Find how many posts were created each month:

SELECT
  strftime('%Y-%m', created_at) AS month,
  COUNT(*) AS post_count
FROM posts
GROUP BY strftime('%Y-%m', created_at)
ORDER BY month DESC;

Result:

monthpost_count
2026-0215
2026-0123
2025-1218

Handling timezones

Timezones are tricky. Most databases store times in UTC (Coordinated Universal Time) and let your application convert to the user’s timezone.

PostgreSQL has good timezone support:

SELECT NOW() AT TIME ZONE 'America/New_York';

SQLite doesn’t have built-in timezone support — you handle it in your application.

MySQL has some timezone functions but they’re limited.

Best practice: Store all dates/times in UTC and convert to local time in your application code.

Common patterns

Find records created today:

WHERE DATE(created_at) = DATE('now')

Find records from this week:

WHERE created_at >= DATE('now', 'weekday 0', '-7 days')

Find records older than 30 days:

WHERE created_at < DATE('now', '-30 days')

Calculate age from birthdate:

SELECT
  name,
  (JULIANDAY('now') - JULIANDAY(birthdate)) / 365.25 AS age
FROM users;

Be careful with date comparisons

If you’re comparing DATETIME columns, make sure you include the time component:

-- ❌ This might not work as expected if created_at includes time
WHERE created_at = '2026-02-22'

-- ✅ Better: extract just the date part
WHERE DATE(created_at) = '2026-02-22'

-- ✅ Or use a range
WHERE created_at >= '2026-02-22' AND created_at < '2026-02-23'

Summary

  • Use DATE, TIME, DATETIME types to store dates and times
  • Get current date/time with DATE('now'), NOW(), etc. (varies by database)
  • Extract parts of dates with strftime() (SQLite), YEAR(), MONTH() (PostgreSQL/MySQL)
  • Do date arithmetic with DATE('now', '+7 days') or INTERVAL
  • Calculate differences with JULIANDAY, DATEDIFF, etc.
  • Format dates with strftime, TO_CHAR, DATE_FORMAT
  • Store times in UTC and convert in your application

Date functions vary significantly between databases, so always check your database’s documentation for the exact syntax.

Share: