Skip to main content

DATETIME

Returns a calculated datetime value from an input datetime and one 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';

Covert datetime to UTC​

SELECT name, timeCreated,
datetime('now', 'utc') as now_utc
FROM google.storage.buckets WHERE project = 'stackql';

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';

Covert a Unix timestamp to a datetime​

SELECT datetime(1576417943, 'unixepoch') as date_from_ts
FROM google.storage.buckets WHERE project = 'stackql';
tip

To covert a datetime to a Unix epoch timestamp see STRFTIME. To calculate differences between two datetime objects see STRFTIME or JULIANDAY.

For more information, see https://www.sqlite.org/lang_datefunc.html