The SQLite datetime() function is a built-in function that is used to manipulate date and time values in SQLite databases. It takes one or more arguments and returns a string representation of the corresponding date and time.
Syntax
The syntax of the datetime() function is as follows:
datetime(timestring, modifier, modifier, ...)
Here, timestring is a string that represents the date and time value in a recognized format. The modifier arguments are optional and can be used to modify the date and time value in various ways.
The timestring argument can be in one of several recognized formats, such as YYYY-MM-DD HH:MM:SS or YYYY-MM-DDTHH:MM:SS.SSSZ. The format of the string must match the format that is specified in the SQLite documentation, or an error will be raised.
The modifier arguments can be used to perform various operations on the date and time value. For example, the ‘+N days’ modifier can be used to add N days to the date, while the ‘-N hours’ modifier can be used to subtract N hours from the time. The full list of available modifiers is described in the SQLite documentation.
The datetime() function can be used in various contexts, such as in SQL queries or in application code that accesses SQLite databases. It is a powerful tool for working with date and time values, and can be used to perform complex calculations and manipulations.
One thing to note about the datetime() function is that it returns a string representation of the date and time value, not a native date and time object. If you need to perform further calculations or comparisons with the date and time value, you may need to convert it to a native date and time object using application code or another SQLite function.
Example
The datetime() function can be called in a variety of ways, depending on the format of the date or time string you’re trying to convert. Here are some examples:
1. To return current date and time in the format “YYYY-MM-DD HH:MM:SS”, uses the following example:
SELECT datetime();
Output: 2023-04-19 09:30:20
SELECT datetime('now');
Output: 2023-04-19 09:30:20
2. To convert a date string in the format “YYYY-MM-DD” to a Unix timestamp, you can use the following syntax:
SELECT datetime('2023-04-19');
This will return a Unix timestamp representing April 19, 2023, at 00:00:00 UTC.
3. To convert a time string in the format “HH:MM:SS” to a Unix timestamp, you can use the following syntax:
SELECT datetime('14:30:00');
This will return a Unix timestamp representing 2:30 PM (14:30) on January 1, 1970, UTC.
4. To convert a date and time string in the format “YYYY-MM-DD HH:MM:SS” to a Unix timestamp, you can use the following syntax:
SELECT datetime('2023-04-19 14:30:00');
This will return a Unix timestamp representing April 19, 2023, at 2:30 PM (14:30) UTC.
In addition to these basic examples, the datetime() function can also handle more complex date and time strings, such as those with time zone offsets or fractional seconds.
Overall, the datetime() function is a useful and flexible tool for working with date and time values in SQLite databases. By understanding its syntax and available modifiers, you can leverage its power to perform a wide range of operations on your data.