What are date and time types and functions in SQLite

SQLite is a relational database management system (RDMS), which has a very simple architecture because it has no server and it stores data on the operating system of the local machine in which it is operating. SQLite supports different functions of Date and Time which can help to manage date and time accordingly. Every database management system either has DateTime data types, functions, or both.

In this write-up, we will discuss the DateTime data type and function in detail with the help of examples.

Data types of Date Time in SQLite

A database uses the term data type for the format of data that can be stored in it, for example, SQLite supports the Integer, Text, Blob, and Real data types. SQLite does not support data types for storing date and time like MySQL, instead, it contains a variety of built-in functions() which are used to store the date and time by using data types; Integer, Real, and Text.

Functions of Date Time in SQLite

The functions take a different number of inputs and return a single output by following a specified process. There are a lot of built-in functions() that are used to obtain some specific results such as DateTime functions.

Types of Date and Time functions in SQLite

There are five different types of built-in functions used in SQLite to store and performs tasks:

date()
time()
datetime()
julianday()
strftime()

These functions are explained in detail one by one with the help of examples.

date() function in SQLite

The date() function is used to get the date in the format of YYYY-MM-DD. We can also modify the date by adding/subtracting days, months, and years, or we can get the date at the time of execution of the function.

The general syntax of using date() is:

DATE(date-time-string, [modifier1, modifier2…, modifierN]);

In the above syntax, a date-time string is a string containing the date on which the function is to be applied and a modifier is a task that has to be performed either subtraction or addition of years, months, and days.

If we want to get the current date, we use the input parameter “now”, for example, run the following command:

SELECT DATE('now');

If we want to extract only date by providing date and time in input parameters, we will use the function as:

SELECT DATE('2021-10-06 12:54:12') AS 'DATE()';

If we want to get the last date of the current month, we will run the following command:

SELECT DATE('now','start of month','+1 month','-1 day') AS 'Last Date of the Month';

We can add the months, years, and days in any date using the function date(). For example, we add the nine years to the current date using date() as:

SELECT DATE('now','+9 year');

Similarly, we can subtract the months, years, and dates as well, for example, right now the month is October, we can get the date of 3 months back using:

SELECT DATE('now','-3 month');

time() function in SQLite

The time() function is used to get the time in the format of HH:MM:SS. We can also modify the time by adding/subtracting hours, minutes, and seconds or we can get the current time, at the time of execution of the function.

The general syntax of using time() is:

TIME(date-time-string, [modifier1, modifier2…, modifierN]);

If we want to get the current time, we use the input parameter “now”, for example, run the following command:

SELECT TIME('now');

If we want to get extract only time by providing date and time in input parameters, we will use the function as:

SELECT TIME('2021-10-06 06:05:45') AS 'TIME()';

If we want to get time after the fifteen minutes of the current time, we will run the following command:

SELECT TIME(),TIME('now','+15 minutes');

The output shows the current time as well as the time after fifteen minutes because we run the two functions, one of the current time using the time(), and the other is after fifteen minutes using the time(‘now’,’+15 minutes’). Similarly, we can get a time before four hours from the current time as:

SELECT TIME('now','-4 hours');

Function datetime() in SQLite

The datetime() function is used to get the time in the format of YYYY:MM:DD HH:MM:SS. We can also modify the date and time by adding/subtracting years, months, days, hours, minutes, and seconds or we can get the current date and time, at the time of execution of the function.

The general syntax of using datetime() is:

datetime(date-time-string, [modifier1, modifier2…, modifierN]);

In the above syntax, a date-time string is a string containing the date or time, on which the function is to be applied and a modifier is a task that has to be performed either subtraction or addition of minutes, seconds, hours, days, years, and months.

To display the current date and time, at the time of execution, we will run the following command:

SELECT datetime('now');

Now we have a table Employee, to display the table, execute the following command:

SELECT * FROM Employee;

Now if we want to find out the dates on which employees are eligible to get the first increment. According to the policy of the company, increment should be rewarded after six months of joining date, so the following SQLite query will be executed:

SELECT emp_id, emp_name, joining_date, DATE(joining_date,'6 months') AS 'Appraisal date' FROM Employee;

In the output, we can see the dates on which the employees will be rewarded their first increment in salary.

julianday() function in SQLite

The julianday() function helps to find out the Julian day of the year and the general syntax of this function is as:

julianday(date-time-string, [modifier1, modifier2…, modifierN])

Similar to the syntax of the time and date functions, it also takes two parameters and returns a single input. Let’s take an example of finding total number of days from birth of a person; if a person is born on the twentieth of August 1995, the total number of days to the present day can easily be displayed using julianday() function:

SELECT julianday('now') - julianday('1995-08-20');

The output is showing, a person has lived for approximately 9544 days approximately.

strftime() function in SQLite

The strftime() function is used to convert the string of date or time, to get the date and time in the format of YYYY:MM:DD HH:MM:SS.

The general syntax of strftime() is:

strftime(format, date-time-string, [modifier1,modifier2…,modifier]);

The above syntax is the same as the syntaxes discussed before, but the new argument in it is “format”, one can define the format in which he wants to have an output.

Symbols	Parameters
Y	year
m	month
d	date
S	second
M	minute
H	hour

For example, we want the current date and time in the following format MM:YYYY:DD SS:MM:HH, we will run the following command:

SELECT strftime('%m/%Y/%d %S/%M/%H','now');

Now we want to display the date and time in this format, YYYY:MM HH:MM, so execute the command as:

SELECT strftime('%Y/%m %H/%M','now');

The range of the date in SQLite is from 0000-01-01 to 9999-12-31, where the format shows Year-Month-Date. Similarly, the range of the time is from 00:00:00 to 23:59:59, where the format is Hours:Minutes:Seconds.

Conclusion

SQLite, like other databases, provides a variety of built-in functions which make it easy to use for different applications. The date and time functions help us provide ease in handling the dates and times in real-time different tasks. This write-up is related to the function of date and time used in SQLite to manage both of them. All the types of functions of date and time are explained in this writeup along with the examples, and also describe the range of date and time functions to which these functions are applicable.