DATETIME
Returns a calculated datetime value from an input datetime and zero or more modifiers.
See also:
[SELECT] [ Date Modifiers ]
Syntax
SELECT DATETIME(datetime_expression [, modifier, ...]) FROM <multipartIdentifier>;
Arguments
datetime_expression
The datetime value.
modifier
Zero or more modifiers to be applied to the datetime_expression. For more information about modifiers, see Date Modifiers
Return Value(s)
Returns a string representing the datetime of the input datetime_expression with the modifier applied.
Examples
Convert UTC to localtime using the localtime modifier
SELECT name, timeCreated,
datetime(timeCreated, 'localtime') as local_time_created
FROM google.storage.buckets WHERE project = 'stackql';
Convert a local time to UTC
The utc modifier treats its input as a local time on the machine running StackQL and converts it to UTC. A value that is already in UTC, such as 'now' or a timestamp with a Z suffix, is returned unchanged.
SELECT datetime('2026-11-20 17:59:24', 'utc') as utc_time;
On a machine whose time zone is UTC+11, this query returns 2026-11-20 06:59:24.
Calculate a date relative to another date
SELECT name, timeCreated,
datetime(timeCreated, '+24 hours') as a_day_later
FROM google.storage.buckets WHERE project = 'stackql';
Get the next specified weekday after a given date
SELECT name, timeCreated,
datetime(timeCreated, 'weekday 0') as following_sunday
FROM google.storage.buckets WHERE project = 'stackql';
Get the start of the month for a given date
SELECT name, timeCreated,
datetime(timeCreated, 'start of month') as start_of_month
FROM google.storage.buckets WHERE project = 'stackql';
Convert a Unix timestamp to a datetime
SELECT datetime(1576417943, 'unixepoch') as date_from_ts
FROM google.storage.buckets WHERE project = 'stackql';
tip
For more information, see https://www.sqlite.org/lang_datefunc.html