SQLite UNIXEPOCH() Function

The SQLite UNIXEPOCH() function takes a date and applies modifiers on it and then returns the date as a UNIX timestamp.

A UNIX timestamp is the number of seconds since 1970-01-01 00:00:00 UTC. This function always returns an integer, even if the input time-value has millisecond precision.

Syntax

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

Parameters

Return Value

Returns the date as a UNIX timestamp.

Example: Simple Date

The SQLite UNIXEPOCH() function can be used to convert a simple date to a UNIX timestamp.

SELECT UNIXEPOCH('2018-08-18');
Result: '1534550400'

SELECT UNIXEPOCH('2018-08-18 08:23:19');
Result: '1534580599'

Example: Current Date and time

The SQLite 'now' time-value can be used to get UNIX timestamp for current date and time.

SELECT UNIXEPOCH('now');
Result: '1649513012'

Example: First day of the month

To get the first day of the month, 'start of month' modifier can be used. See the example below:

SELECT UNIXEPOCH('2018-08-18 08:23:19', 'start of month');
Result: '1533081600'

SELECT UNIXEPOCH('now', 'start of month');
Result: '1648771200'

Example: Last day of the month

To get the last day of the month, first, the 'start of month' modifier is used to calculate the start day of the month and then 1 month is added and then 1 day is subtracted.

SELECT UNIXEPOCH('2018-08-18', 'start of month', '+1 month', '-1 day');
Result: '1535673600'

SELECT UNIXEPOCH('now', 'start of month', '+1 month', '-1 day');
Result: '1651276800'

Example: Adding/Subtracting years

To add/subtract years from a time-value, [+-]NNN years modifier can be used. In the example below 2 years is added and subtracted from a given time-value.

SELECT UNIXEPOCH('2018-08-18 08:23:19', '+2 years');
Result: '1597738999'

SELECT UNIXEPOCH('2018-08-18 08:23:19', '-2 years');
Result: '1471508599'

Example: Adding/Subtracting days

To add/subtract days drom a time-value, [+-]NNN days modifier can be used. In the example below 10 days is added and subtracted from a given time-value.

SELECT UNIXEPOCH('2018-08-18 08:23:19', '+10 days');
Result: '1535444599'

SELECT UNIXEPOCH('2018-08-18 08:23:19', '-10 days');
Result: '1533716599'

Example: Adding/Subtracting hours

To add/subtract hours from a time-value, [+-]NNN hours modifier can be used. In the example below 5 hours is added and subtracted from a given time-value.

SELECT UNIXEPOCH('2018-08-18 08:23:19', '+5 hours');
Result: '1534598599'

SELECT UNIXEPOCH('2018-08-18 08:23:19', '-5 hours');
Result: '1534562599'

Example: Adding/Subtracting minutes

To add/subtract minutes from a time-value, [+-]NNN minutes modifier can be used. In the example below 30 minutes is added and subtracted from a given time-value.

SELECT UNIXEPOCH('2018-08-18 08:23:19', '+30 minutes');
Result: '1534582399'

SELECT UNIXEPOCH('2018-08-18 08:23:19', '-30 minutes');
Result: '1534578799'