Date and Time Functions

Turso provides a complete set of date and time functions compatible with SQLite. All functions use the proleptic Gregorian calendar and assume UTC unless the localtime modifier is applied. All date and time functions accept a time value in various formats, apply optional modifiers, and return results as TEXT, REAL, or INTEGER depending on the function.

Functions

date()

Returns the date as TEXT in YYYY-MM-DD format.

date(time-value, modifier, modifier, ...)

Parameter	Type	Description
`time-value`	TEXT, REAL, or INTEGER	A time value in any supported format
`modifier`	TEXT	Zero or more modifiers applied left to right

Returns: TEXT in YYYY-MM-DD format, or NULL if any argument is invalid.

SELECT date('now');                          -- '2025-06-15'
SELECT date('2025-06-15 14:30:00');          -- '2025-06-15'
SELECT date('2025-06-15', '+1 month');       -- '2025-07-15'
SELECT date('2025-06-15', 'start of year');  -- '2025-01-01'

time()

Returns the time as TEXT in HH:MM:SS format.

time(time-value, modifier, modifier, ...)

Returns: TEXT in HH:MM:SS format, or NULL if any argument is invalid.

SELECT time('now');                       -- '14:30:00'
SELECT time('2025-06-15 14:30:00');       -- '14:30:00'
SELECT time('14:30:00', '+2 hours');      -- '16:30:00'
SELECT time('14:30:00', '+90 minutes');   -- '16:00:00'

datetime()

Returns the date and time as TEXT in YYYY-MM-DD HH:MM:SS format.

datetime(time-value, modifier, modifier, ...)

Returns: TEXT in YYYY-MM-DD HH:MM:SS format, or NULL if any argument is invalid.

SELECT datetime('now');                           -- '2025-06-15 14:30:00'
SELECT datetime('2025-06-15', '+1 day', '+6 hours'); -- '2025-06-16 06:00:00'
SELECT datetime(1718458200, 'unixepoch');          -- '2024-06-15 14:30:00'

julianday()

Returns the Julian day number as REAL — the number of days since noon on November 24, 4714 B.C. (proleptic Gregorian calendar).

julianday(time-value, modifier, modifier, ...)

Returns: REAL, or NULL if any argument is invalid.

SELECT julianday('2025-06-15');
-- 2460811.5

-- Days between two dates
SELECT julianday('2025-12-25') - julianday('2025-06-15');
-- 193.0

unixepoch()

Returns the Unix timestamp as INTEGER — seconds since 1970-01-01 00:00:00 UTC.

unixepoch(time-value, modifier, modifier, ...)

Returns: INTEGER (seconds since epoch), or REAL with fractional seconds if the subsec modifier is used. Returns NULL if any argument is invalid.

SELECT unixepoch('now');                                  -- 1718458200
SELECT unixepoch('2025-06-15 14:30:00');                 -- 1750001400
SELECT unixepoch('2025-06-15 14:30:00.123', 'subsec');   -- 1750001400.123

strftime()

Returns a formatted date/time string according to the given format.

strftime(format, time-value, modifier, modifier, ...)

Parameter	Type	Description
`format`	TEXT	A format string with substitution codes
`time-value`	TEXT, REAL, or INTEGER	A time value in any supported format
`modifier`	TEXT	Zero or more modifiers applied left to right

Returns: TEXT, or NULL if any argument is invalid. The other date/time functions are equivalent to specific strftime calls:

Function	Equivalent strftime
`date(...)`	`strftime('%Y-%m-%d', ...)`
`time(...)`	`strftime('%H:%M:%S', ...)`
`datetime(...)`	`strftime('%Y-%m-%d %H:%M:%S', ...)`
`julianday(...)`	`strftime('%J', ...)` (as TEXT)
`unixepoch(...)`	`strftime('%s', ...)` (as TEXT)

SELECT strftime('%Y', 'now');                              -- '2025'
SELECT strftime('%m/%d/%Y', '2025-06-15');                -- '06/15/2025'
SELECT strftime('%H:%M', '2025-06-15 14:30:00');          -- '14:30'
SELECT strftime('%Y-%m-%d %H:%M:%f', '2025-06-15 14:30:00.123'); -- '2025-06-15 14:30:00.123'
SELECT strftime('%s', '2025-06-15 14:30:00');             -- '1750001400'

strftime Format Codes

Code	Description	Example
`%d`	Day of month (01–31)	`15`
`%e`	Day of month, space-padded ( 1–31)	`15`
`%f`	Fractional seconds (SS.SSS)	`00.123`
`%F`	ISO 8601 date (YYYY-MM-DD)	`2025-06-15`
`%H`	Hour (00–23)	`14`
`%I`	Hour (01–12)	`02`
`%j`	Day of year (001–366)	`166`
`%J`	Julian day number	`2460811.5`
`%k`	Hour, space-padded ( 0–23)	`14`
`%l`	Hour, space-padded ( 1–12)	`2`
`%m`	Month (01–12)	`06`
`%M`	Minute (00–59)	`30`
`%p`	AM or PM	`PM`
`%P`	am or pm	`pm`
`%R`	Time as HH:MM	`14:30`
`%s`	Seconds since Unix epoch	`1750001400`
`%S`	Seconds (00–59)	`00`
`%T`	Time as HH:MM:SS	`14:30:00`
`%u`	Day of week (1=Monday, 7=Sunday)	`7`
`%w`	Day of week (0=Sunday, 6=Saturday)	`0`
`%W`	Week of year (00–53)	`23`
`%Y`	Year (0000–9999)	`2025`
`%%`	Literal `%`	`%`

timediff()

Returns the difference between two time values. The result represents the time that must be added to time2 to produce time1.

timediff(time1, time2)

Returns: TEXT in (+|-)YYYY-MM-DD HH:MM:SS.SSS format, or NULL if either argument is invalid.

SELECT timediff('2025-06-15', '2025-06-10');
-- '+0000-00-05 00:00:00.000'

SELECT timediff('2025-01-01', '2024-01-01');
-- '+0001-00-00 00:00:00.000'

SELECT timediff('2025-06-15 14:30:00', '2025-06-15 10:00:00');
-- '+0000-00-00 04:30:00.000'

SELECT timediff('2024-01-01', '2025-06-15');
-- '-0001-05-14 00:00:00.000'

Time Value Formats

All date and time functions accept time values in these formats:

Format	Example	Description
`YYYY-MM-DD`	`'2025-06-15'`	Date only (time defaults to 00:00:00)
`YYYY-MM-DD HH:MM`	`'2025-06-15 14:30'`	Date and time (seconds default to 00)
`YYYY-MM-DD HH:MM:SS`	`'2025-06-15 14:30:00'`	Date and time with seconds
`YYYY-MM-DD HH:MM:SS.SSS`	`'2025-06-15 14:30:00.123'`	Date and time with fractional seconds
`YYYY-MM-DDTHH:MM`	`'2025-06-15T14:30'`	ISO 8601 with T separator
`YYYY-MM-DDTHH:MM:SS`	`'2025-06-15T14:30:00'`	ISO 8601 with T separator
`YYYY-MM-DDTHH:MM:SS.SSS`	`'2025-06-15T14:30:00.123'`	ISO 8601 with T separator
`HH:MM`	`'14:30'`	Time only (date defaults to 2000-01-01)
`HH:MM:SS`	`'14:30:00'`	Time only with seconds
`HH:MM:SS.SSS`	`'14:30:00.123'`	Time only with fractional seconds
`now`	`'now'`	Current UTC date and time
Julian day	`2460811.5`	REAL number of days since Julian epoch
Unix timestamp	`1718458200`	INTEGER seconds since 1970-01-01; requires `'unixepoch'` modifier

When passing a Unix timestamp as the time value, include the 'unixepoch' modifier. Without it, the integer is interpreted as a Julian day number, producing unexpected results.

-- Correct: use 'unixepoch' modifier
SELECT datetime(1718458200, 'unixepoch');
-- '2024-06-15 14:30:00'

-- Incorrect: treated as Julian day
SELECT datetime(1718458200);
-- NULL or a date billions of years in the future

Modifiers

Modifiers transform the time value. Multiple modifiers are applied left to right. If any modifier is invalid, the function returns NULL.

Offset Modifiers

Modifier	Description
`NNN days`	Add NNN days
`NNN hours`	Add NNN hours
`NNN minutes`	Add NNN minutes
`NNN seconds`	Add NNN seconds (can be fractional)
`NNN months`	Add NNN months
`NNN years`	Add NNN years

NNN can be positive or negative. The + sign is optional for positive values.

SELECT date('2025-06-15', '+7 days');           -- '2025-06-22'
SELECT datetime('2025-06-15 14:30:00', '-6 hours'); -- '2025-06-15 08:30:00'
SELECT date('2025-01-31', '+1 months');         -- '2025-03-03' (overflow rolls forward)

Time Offset Modifier

A time offset in +HH:MM or -HH:MM format adds or subtracts the specified hours and minutes.

SELECT time('14:30:00', '+05:30');              -- '20:00:00'
SELECT datetime('2025-06-15 14:30:00', '-08:00'); -- '2025-06-15 06:30:00'

Start-of Modifiers

Modifier	Description
`start of day`	Sets time to 00:00:00, keeps date
`start of month`	Sets to first day of the month, time to 00:00:00
`start of year`	Sets to January 1 of the year, time to 00:00:00

SELECT datetime('2025-06-15 14:30:00', 'start of day');   -- '2025-06-15 00:00:00'
SELECT date('2025-06-15', 'start of month');               -- '2025-06-01'
SELECT date('2025-06-15', 'start of year');                -- '2025-01-01'

Weekday Modifier

weekday N advances the date to the next occurrence of weekday N, where 0 = Sunday, 1 = Monday, …, 6 = Saturday. If the current date is already that weekday, it is unchanged.

-- Next Monday from June 15, 2025 (a Sunday)
SELECT date('2025-06-15', 'weekday 1');  -- '2025-06-16'

-- Next Friday
SELECT date('2025-06-15', 'weekday 5');  -- '2025-06-20'

Interpretation Modifiers

Modifier	Description
`unixepoch`	Interpret the time value as seconds since 1970-01-01
`julianday`	Interpret the time value as a Julian day number
`auto`	Automatically detect whether the value is a Unix timestamp or Julian day

SELECT datetime(1718458200, 'unixepoch');   -- '2024-06-15 14:30:00'
SELECT datetime(2460811.5, 'julianday');   -- '2025-06-15 00:00:00'
SELECT datetime(0, 'unixepoch');           -- '1970-01-01 00:00:00'

Timezone Modifiers

Modifier	Description
`localtime`	Convert from UTC to local time
`utc`	Convert from local time to UTC

SELECT datetime('2025-06-15 14:30:00', 'localtime');
-- '2025-06-15 07:30:00' (example for UTC-7)

Month Rounding Modifiers

Modifier	Description
`ceiling`	When month arithmetic overflows, advance to the first day of the next month
`floor`	When month arithmetic overflows, use the last day of the target month

-- Default: overflow rolls forward
SELECT date('2025-01-31', '+1 months');               -- '2025-03-03'

-- With floor: clamp to last day of target month
SELECT date('2025-01-31', 'floor', '+1 months');      -- '2025-02-28'

-- With ceiling: advance to first of next month
SELECT date('2025-01-31', 'ceiling', '+1 months');    -- '2025-03-01'

Subsec Modifier

Causes unixepoch() to return REAL with fractional seconds instead of truncating to INTEGER.

SELECT unixepoch('2025-06-15 14:30:00.456', 'subsec');
-- 1750001400.456

Practical Examples

Current Date and Time

SELECT date('now');                   -- Current UTC date
SELECT time('now');                   -- Current UTC time
SELECT datetime('now');               -- Current UTC date and time
SELECT datetime('now', 'localtime');  -- Current local date and time

Date Arithmetic

SELECT date('now', '+1 day');                               -- Tomorrow
SELECT date('now', '+90 days');                             -- 90 days from now
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- Last day of this month
SELECT date('now', 'start of month', 'weekday 1');          -- First Monday of this month

Converting Between Formats

-- Unix timestamp to readable
SELECT datetime(1718458200, 'unixepoch');

-- Readable to Unix timestamp
SELECT unixepoch('2025-06-15 14:30:00');

-- Date to Julian day and back
SELECT julianday('2025-06-15');
SELECT date(2460811.5);

Grouping by Time Period

-- Orders per month
SELECT
    strftime('%Y-%m', order_date) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

-- Events by day of week
SELECT
    CASE strftime('%w', event_date)
        WHEN '0' THEN 'Sunday'
        WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'
        WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday'
        WHEN '5' THEN 'Friday'
        WHEN '6' THEN 'Saturday'
    END AS day_name,
    COUNT(*) AS event_count
FROM events
GROUP BY strftime('%w', event_date);

Filtering by Date Range

-- Records from the last 30 days
SELECT * FROM logs
WHERE timestamp >= datetime('now', '-30 days');

-- Records from a specific month
SELECT * FROM orders
WHERE order_date >= '2025-06-01'
  AND order_date < '2025-07-01';

Elapsed Time

-- Days until a date
SELECT julianday('2025-12-25') - julianday('now') AS days_until_christmas;

-- Seconds between two timestamps
SELECT
    unixepoch('2025-06-15 18:00:00') - unixepoch('2025-06-15 14:30:00')
    AS seconds_elapsed;
-- 12600

Overview

Statements

Functions

Pragmas

Date and Time Functions

Functions

date()

time()

datetime()

julianday()

unixepoch()

strftime()

strftime Format Codes

timediff()

Time Value Formats

Modifiers

Offset Modifiers

Time Offset Modifier

Start-of Modifiers

Weekday Modifier

Interpretation Modifiers

Timezone Modifiers

Month Rounding Modifiers

Subsec Modifier

Practical Examples

Current Date and Time

Date Arithmetic

Converting Between Formats

Grouping by Time Period

Filtering by Date Range

Elapsed Time

See Also

Build docs developers (and LLMs) love

Overview

Statements

Functions

Pragmas

​Functions

​date()

​time()

​datetime()

​julianday()

​unixepoch()

​strftime()

​strftime Format Codes

​timediff()

​Time Value Formats

​Modifiers

​Offset Modifiers

​Time Offset Modifier

​Start-of Modifiers

​Weekday Modifier

​Interpretation Modifiers

​Timezone Modifiers

​Month Rounding Modifiers

​Subsec Modifier

​Practical Examples

​Current Date and Time

​Date Arithmetic

​Converting Between Formats

​Grouping by Time Period

​Filtering by Date Range

​Elapsed Time

​See Also

Build docs developers (and LLMs) love

Functions

date()

time()

datetime()

julianday()

unixepoch()

strftime()

strftime Format Codes

timediff()

Time Value Formats

Modifiers

Offset Modifiers

Time Offset Modifier

Start-of Modifiers

Weekday Modifier

Interpretation Modifiers

Timezone Modifiers

Month Rounding Modifiers

Subsec Modifier

Practical Examples

Current Date and Time

Date Arithmetic

Converting Between Formats

Grouping by Time Period

Filtering by Date Range

Elapsed Time

See Also