Dev Corner

In SQLite there is no class used for storing date and time. But there are still some handful functions, which are for working with time information.

1. How to get the current date and time ?

SELECT datetime(‘now’)

In this query the string ‘now’, is converted into the current date and time in the format YYYY-MM-DD HH:MM:SS. But SQLite does not support date arithmetic for this format.

2. Receive the current time as an integer

SELECT strftime('%s',’now’)

This query uses the function strftime, which returns the current time as Unix Time – the passed seconds since 1970-01-01. Universal Coordinated Time (UTC) is used. This is much more useful format, because it is an integer data, which is supported by SQLite data class. This means that arithmetical calculations could be made using the result from this query. Also, this data in this format could be easily ordered and stored. Even, if we want to store the moment, that a row is inserted in a table, we could use a column for the date and time, using a default value the result of this function.

3. Get the result back

SELECT datetime(strftime('%s',’now’),'localtime', 'unixepoch')

This
This query uses the function datetime to convert the integer result from the strftime function, which is time in Universal Coordinated Time (UTC). So to set it to the system time the ‘localtime’ modificator is used. The result of this reverting function is again in the human readable format – YYYY-MM-DD HH:MM:SS.

This are the steps that could be used, to emulate a date and time class in SQLite, which could be a great miss, if we want to use our database to store time information – a very frequent practice.