Databases with SQLite

Date & Time Functions

There are several built-in functions available to you including math, string, date & time, window, JSON.

Review this page for in-depth explanation and examples of them.

On this page we will look at some functions dealing with date and time. Put your thinking cap on as this can be complicated. A wise man once said 'Working with time values correctly, in all but the simplest cases, is REALLY HARD and deserves serious respect.'

SQLite has seven functions specifically for mangling various date or time values.

• date()
  returns the date as text in this format: YYYY-MM-DD
• time()
  returns returns the time as text in formatted as HH:MM:SS or as HH:MM:SS.SSS if the subsec modifier is used.
• datetime()
  returns the date and time formatted as YYYY-MM-DD HH:MM:SS or as YYYY-MM-DD HH:MM:SS.SSS if the subsec modifier is used.
• julianday()
  returns the Julian day - the fractional number of days since noon in Greenwich on November 24, 4714 B.C.
• unixepoch()
  returns a unix timestamp - the number of seconds since 1970-01-01 00:00:00 UTC. The unixepoch() function normally returns an integer number of seconds, but with the optional subsec modifier it will return a floating point number which is the fractional number of seconds.
• strftime()
  returns the date formatted according to the format string specified as the first argument.
  There are over 2 dozen specifications, only a few of which we will discuss.
• timediff(A,B)
  returns a string that describes the amount of time that must be added to B in order to reach time A.

Before jumping into explanations about what each of the above functions do, it is important to understand how SQLite groks these concepts.

So depending on which of those you work with, that will determine the datatype in a table - usually either string or numeric.

Known as time values, they are stored as:

• ISO 8601
  eg: '2025-05-29 14:16:00'
• Julian day number
  The number of days including fractional days since -4713-11-24 12:00:00
  eg: '2460825.09444444'
• Unix timestamp
  The number of seconds including fractional seconds since 1970-01-01 00:00:00
  eg: '1748528160'

Obvously some are human-readable, some not.

The reason for the functions is to be able to present something in the best manner for the job, either storing or displaying.

date

Handling dates? The simplest example would be:

select date();
┌────────────┐
│   date()   │
├────────────┤
│ 2025-04-09 │
└────────────┘

So to enter the current date into a table:

insert into cds (notes)values(date());

select notes from cds order by id desc limit 1;
┌────────────┐
│   notes    │
├────────────┤
│ 2025-04-09 │
└────────────┘

select substr(date(),1,4) as year;
┌──────┐
│ year │
├──────┤
│ 2025 │
└──────┘

There are also some modifiers available, which allow us to determine a future or past date.

What's the last day of the current month?

SELECT date('now','start of month','+1 month','-1 day') as EndOfMonth;
┌────────────┐
│ EndOfMonth │
├────────────┤
│ 2025-04-30 │
└────────────┘

Combining with concat ...

select concat(date('now'),"_", time('now')) as "Right Now";
┌─────────────────────┐
│      Right Now      │
├─────────────────────┤
│ 2025-04-09_22:39:03 │
└─────────────────────┘

I know you really want to know how many days have elapsed since 4713 BC:

select julianday('now') as "Julian Day";
┌─────────────────┐
│   Julian Day    │
├─────────────────┤
│ 2460779.1598441 │
└─────────────────┘

strftime

This function is used to control (prettify) the output of some of the values that get returned from the date and time functions.

As mentioned above, there are several options available, depending on your specific needs. We'll show you a few to get the engine started.

The formatting of this function is similar to others:

select strftime('%J','now') as "Julian Day";
┌───────────────────┐
│    Julian Day     │
├───────────────────┤
│ 2460779.161684572 │
└───────────────────┘

select strftime('%j','now') as "Day of Year";
┌─────────────┐
│ Day of Year │
├─────────────┤
│ 103         │
└─────────────┘

select strftime('%w','now') as "Day of The Week";
┌─────────────────┐
│ Day of The Week │
├─────────────────┤
│ 0               │
└─────────────────┘

select strftime('%u','now') as "Altered Day of The Week";
┌─────────────────────────┐
│ Altered Day of The Week │
├─────────────────────────┤
│ 7                       │
└─────────────────────────┘

select strftime('%w','now','+1 days') as Tomorrow;
┌──────────┐
│ Tomorrow │
├──────────┤
│ 1        │
└──────────┘

select date('now','start of month');
┌──────────────────────────────┐
│ date('now','start of month') │
├──────────────────────────────┤
│ 2025-04-01                   │
└──────────────────────────────┘

select date('now','+5 months','+274 days') as 'The Future';
┌────────────┐
│ The Future │
├────────────┤
│ 2026-06-14 │
└────────────┘

select time('now','auto');
┌────────────────────┐
│ time('now','auto') │
├────────────────────┤
│ 17:01:48           │
└────────────────────┘
sqlite> select time('now','subsec');
┌──────────────────────┐
│ time('now','subsec') │
├──────────────────────┤
│ 17:02:08.474         │
└──────────────────────┘
sqlite> select time('now','subsecond');
┌─────────────────────────┐
│ time('now','subsecond') │
├─────────────────────────┤
│ 17:02:14.311            │
└─────────────────────────┘
sqlite> select time('now','utc');
┌───────────────────┐
│ time('now','utc') │
├───────────────────┤
│ 17:02:54          │
└───────────────────┘
select date('now','+14 days') as '2 weeks';
┌────────────┐
│  2 weeks   │
├────────────┤
│ 2025-04-27 │
└────────────┘

select strftime("%Y-%m-%d %H:%M:%f", "now") as 'RIGHT NOW!';
┌─────────────────────────┐
│       RIGHT NOW!        │
├─────────────────────────┤
│ 2025-04-13 17:17:53.656 │
└─────────────────────────┘