Date and time types and functions in SQLite: SQLite is a relational database management system (RDMS) that is architected in a very simple way because it has no servers and stores data on the operating system of the local machine running it. SQLite supports different date and time features that can help manage dates and times accordingly. Every database management system has a DateTime data type, a function, or both.
SQLite Date and Time Functions: In this article, we’ll discuss the DateTime data type and function in detail with the help of examples.
The data type of the date and time in SQLite
Databases use term data types to represent data formats that can be stored in them, for example, SQLite supports Integer, Text, Blob, and Real data types. Unlike MySQL, SQLite supports data types that store dates and times, but includes a variety of built-in functions () that use data types to store dates and times; integers, real numbers, and text.
Date-time functions in SQLite
These functions take a different number of inputs and follow a specified process to return a single output. There are a lot of built-in functions() that can be used to get some specific results, such as the DateTime function.
The type of date and time function in SQLite
There are five different types of built-in functions used in SQLite to store and execute tasks:
- date()
- time()
- datetime()
- julianday()
- strftime()
These functions are explained in detail through examples.
date() function in SQLite
SQLite date and time function: The date() function is used to get a date in the format YYYY-MM-DD. We can also modify the date by adding or subtracting days, months, years, or we can get the date when the function is executed.
How does SQLite use date and time functions? The general syntax for using date() is:
DATE(date-time-string, [modifier1, modifier2…, modifierN]);In the syntax above, a datetime string is a string that contains the date to which the function is to be applied, and a modifier is a task that must perform the subtraction or addition of the year, month, and day.
If we want to get the current date, we use the input parameter “now”, for example, run the following command:
SELECT DATE('now');
SQLite date and time function usage example: If we only want to extract the date by providing the date and time in the input parameters, we will use this function:
SELECT DATE('2021-10-06 12:54:12') AS 'DATE()';
If we want to get the last day of the month, we’ll run the following command:
SELECT DATE('now','start of month','+1 month','-1 day') AS 'Last Date of the Month';
How does SQLite use date and time functions? We can use the date() function to add months, years, and days to any date. For example, we use date() to add nine years to the current date, as follows:
SELECT DATE('now','+9 year');
Similarly, we can also subtract the month, year, and date, for example, now that the month is October, we can get the date of 3 months using the following method:
SELECT DATE('now','-3 month');
time() function in SQLite
The date and time type and function in SQLite: the time() function is used to get the time in the format HH:MM:SS. We can also modify the time by adding/subtracting hours, minutes, and seconds, or we can get the current time when the function is executed.
The general syntax for using time() is:
TIME(date-time-string, [modifier1, modifier2…, modifierN]);In the above syntax, a datetime string is a string that contains the date to which the function is to be applied, while a modifier is a task that must perform the subtraction or addition of hours, minutes, and seconds.
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 only the extraction time by providing the date and time in the input parameters, we’ll use the function:
SELECT TIME('2021-10-06 06:05:45') AS 'TIME()';
If we want to get the time fifteen minutes after 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 fifteen minutes later because we ran two functions, one using time() and the other using time(‘now’, ‘+15 minutes’). Similarly, we can get the time four hours ago from the current time:
SELECT TIME('now','-4 hours');
Function datetime() in SQLite
SQLite date and time function: The datetime() function is used to get the time in the format YYYY:MM:DD HH:MM:SS. We can also modify the date and time by adding/subtracting the year, month, day, hour, minute, and second, or we can get the current date and time when executing the function.
SQLite date and time function usage example: The general syntax for using datetime() is:
datetime(date-time-string, [modifier1, modifier2…, modifierN]);In the syntax above, a datetime string is a string that contains a date or time on which the function will be applied, and a modifier is a task that must perform the subtraction or addition of minutes, seconds, hours, days, years, and months.
To display the current date and time, when executed, 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 date when an employee is eligible for the first increment. According to the company’s policy, increments should be rewarded after six months from the date of joining, so the following SQLite queries 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 date when the employee received the first pay increase.
julianday() function in SQLite
Date and time types and functions in SQLite: The julianday() function helps to find out the Julian days of the year, and the general syntax of the function is as follows:
julianday(date-time-string, [modifier1, modifier2…, modifierN])Similar to the syntax of the time and date function, it also accepts two arguments and returns one input. Let’s take an example to calculate the total number of days after a person is born, if a person was born on August 20, 1995, then the total number of days up to today can be easily displayed using the julianday() function:
SELECT julianday('now') - julianday('1995-08-20');
The output shows that a person lived for about 9544 days.
strftime() function in SQLite
SQLite date and time function: The strftime() function is used to convert a date or time string to get a date and time in the format YYYY:MM:DD HH:MM:SS.
The general syntax of strftime() is:
strftime(format, date-time-string, [modifier1,modifier2…,modifier]);The syntax above is the same as the one discussed earlier, but the new parameter in it is “Format”, which defines the output format he wants.
| symbol | parameter |
|---|---|
| Y | year |
| m | month |
| d | date |
| S | second |
| M | minute |
| H | hour |
For example, if we want the current date and time to be 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');
SQLite date and time function usage example: Now we want to display the date and time in this format, YYYY:MM HH:MM, so the execution command is:
SELECT strftime('%Y/%m %H/%M','now');
Dates in SQLite range from 0000-01-01 to 9999-12-31 in the format Year-Month-Date. Similarly, the time range is 00:00:00 to 23:59:59 in the format Hours:Minutes:Seconds.
Date and time types and function usage in SQLite conclusion
SQLite, like other databases, offers a variety of built-in functions that make it easy to use for different applications. The date and time functions help us easily handle the date and time in different tasks in real-time. This article is related to the date and time features used to manage them in SQLite. In this post, all types of date and time functions are explained along with the examples and the range of date and time functions to which these functions are applicable is described.
