Date and Time data types in SQLite

SQLite is a popular database management system used in various applications, including mobile devices and web browsers. It provides a wide range of date and time types to store and manipulate date and time data in a database. In this article, we will discuss the various date and time types supported by SQLite and how to work with them.

Date and time types

SQLite supports several date and time types, including DATE, TIME, DATETIME, and TIMESTAMP. These types represent a combination of date and time or just the date or time separately.

The DATE type stores a date value in the format 'YYYY-MM-DD'. You can use it to store dates like '2023-03-05' or '1996-11-06'. This type supports any date ranging from '0000-01-01' to '9999-12-31'.

The TIME type stores a time value in the format 'hh:mm:ss'. You can use it to store the time of the day or to store the time that will represent the interval between two dates. The range supported by this type is from '00:00:00' to '23:59:59.999999'.

The DATETIME type stores both date and time in the format 'YYYY-MM-DD hh:mm:ss'. It supports the range from '0000-01-01 00:00:00' to '9999-12-31 23:59:59.999999'.

The TIMESTAMP type stores the number of seconds elapsed since 1970-01-01 00:00:00 UTC. It supports the range from '0000-01-01 00:00:00' UTC to '9999-12-31 23:59:59' UTC.

Current date and time

It's pretty easy to get the current date and time with SQLite. We can use the built-in functions DATE('now') and TIME('now').

These functions return the current date and time in the format 'YYYY-MM-DD' and 'hh:mm:ss', respectively.

To get the current date and time in one call, you can use the built-in function DATETIME('now'), which returns the current date and time in the format 'YYYY-MM-DD hh:mm:ss'.

Please see the examples below:

SELECT DATE('now'); -- Returns the current date in the format 'YYYY-MM-DD'

SELECT TIME('now'); -- Returns the current time in the format 'hh:mm:ss'

SELECT DATETIME('now'); -- Returns the current date and time in the format 'YYYY-MM-DD hh:mm:ss'

Date and time difference

There is another useful function, that could help us return the difference between two dates or times: it is a built-in function julianday(). This function returns the Julian day number for a given date or time.

In order to get the result, you have to subtract the Julian day numbers of the two dates or times to get the difference in days. If you want the time difference between two days to be returned in a specific format – use the built-in function strftime(). For example, you can use the format '%S' to get the difference in seconds.

Below are examples of how to write the function:

SELECT julianday('2023-03-05') - julianday('2023-03-01');
-- Returns the difference in days between two dates

SELECT strftime('%S', '2023-03-05 12:00:00') - strftime('%S', '2023-03-05 10:00:00');
-- Returns the difference in seconds between two times

Parts of dates

Let's look in detail at the built-in function strftime() with help of which, we can extract a specific part of some date.

This function takes a format string as its first argument and a date or time value as its second argument. You can use various format specifiers to extract different parts of a date or time.

Below there is a table with specifiers and extracted data:

Please see the code examples below:

SELECT strftime('%Y', '2023-03-05'); -- Returns the year from a date in the format 'YYYY'

SELECT strftime('%m', '2023-03-05'); -- Returns the month from a date in the format 'MM'

SELECT strftime('%d', '2023-03-05'); -- Returns the day from a date in the format 'DD'

SELECT strftime('%H', '2023-03-05 12:00:00'); -- Returns the hour from a time in the format 'hh'

SELECT strftime('%M', '2023-03-05 12:30:00'); -- Returns the minute from a time in the format 'mm'

SELECT strftime('%S', '2023-03-05 12:30:30'); -- Returns the second from a time in the format 'ss'

Adding and subtracting dates

To add or subtract a specific number of days or time units from a date or time value, you can use the built-in functions date(), time(), datetime().

This function takes a date or time value as its first argument and a modifier string as its second argument.

The modifier string is a combination of a number and a unit specifier. For example, the modifier string '1 day' adds one day to a date value, and the modifier string '2 hours' adds two hours to a time value

SELECT date('2023-03-01', '+1 day'); -- Adds one day to a date value

SELECT datetime('2023-03-05 10:00:00', '+2 hours'); -- Adds two hours to a time value

SELECT datetime('2023-03-05 10:00:00', '-30 minutes'); -- Subtracts 30 minutes from a time value

Time zone support

In SQLite, you can use the built-in functions to manipulate and convert date and time values. However, SQLite does not support time zone conversion natively, so you will need to handle time zone conversion manually if needed.

To convert dates from one time zone to another, you can apply the built-in function strftime(). With its help, we can format a date or time value according to a specified format string. We can use the '%s' format specifier to convert a date or time value to the number of seconds elapsed since 1970-01-01 00:00:00 UTC, and then convert it to another time zone using the appropriate offset.

For example, the query below converts the given date and time from the 'UTC' time zone to the 'US/Eastern' timezone:

SELECT strftime('%Y-%m-%d %H:%M:%S', datetime('2008-05-15 12:00:00', 'utc', '+4 hours'));

To set the time zone for the current session, you can use the PRAGMAstatement:

PRAGMA timezone = timezone;

You can use offsets in the inclusive range from '-23:59' to '+23:59' as the timezone value.

Conclusion

In this topic, we've learned about SQLite date and time functions and data types, commonly used for storing and manipulating temporal values. SQLite offers a variety of date & time functions that you can use to perform operations on temporal data. Some of the functions covered in this topic include date, time, datetime, strftime, and julianday.

Now that we've covered the basics of date & time functions in SQLite, it's time to put that knowledge into practice!