SQLite DateTime Comparison

1. Compare Two Dates With the DateTime Data Type
2. Use the strftime() Function to Compare Two Dates in SQLite

SQLite database uses different types of data to store, including integers, strings, and some date-time formats. For date-time storing, there are various types such as date, smalldatetime, DateTime, datetime2, etc.

Generally, we use the WHERE clause for comparison in the SQLite database and retrieve the required data.

The question is how to compare different types of datetime formats inside the SQL database. Remember, there is no direct way to compare the dates and times.

Compare Two Dates With the DateTime Data Type

In SQLite, there is no such way to compare dates directly. However, there are built-in functions for dates and times in SQLite, which can store dates and times in the text, integers, or real values.

They are application-dependent, and we can choose the format for built-in time and date functions.

1. Text takes the ISO8601 strings having the YYYY-MM-DD HH:MM:SS.SSS format.
2. Integer takes the Unix time, which has the 1970-01-01 00:00:00 UTC format.

So, to compare, we usually convert the date and time into a string in a particular format to make it consistent. For example, we store the date in the YYYY-MM-DD hh:mm:ss format.

If we consistently store the date and time in this format, we can easily compare the date and time. We can follow the statement below to demonstrate a simple comparison example with dates.

SELECT * FROM name_of_the_table WHERE comparsion_date < '2020-04-05 00:00:00';

Although we can see that the comparison is not numeric, we have an alphabetic comparison. This is correct if the stored date format and the comparison date have similar formats.

Here, we need to remember two important points.

1. It is suggested to store time in a 24-hour format as it will be easy for us to compare them.
2. It is not necessary to store the date and time in a single column, but the important factor is consistency in the format. Because if the dates and times are in separate columns, then the format should be YYYY-MM-DD for the date column and hh:mm:ss for the time column.

So, if the date and time are in separate columns, we write the SQL query as follows. The key point is to make a date and time format consistent throughout the database.

SELECT * FROM name_of_the_table WHERE comparsion_date = '2020-05-03' AND comparison_time = '08:03:11';

Another reason the format, YYYY-MM-DD hh:mm:ss, is preferred is because when we sort from the highest to the lowest index, that is, from hours to seconds, the comparison is very easy, and performance won’t be degraded.

Use the strftime() Function to Compare Two Dates in SQLite

In SQLite, we use the strftime() function to return the date in a specified format supplied to the argument. The second argument returns the time string in a specified format; the rest are modifiers for getting data with different results.

The syntax of the strftime() function is as follows.

strftime(date_format, time_string, remaining_modifiers, ...)

To compare the date and time using the strftime() function, we use the BETWEEN and AND operators in the SQL statement to find a particular date in a specific period.

SELECT * FROM name_of_the_table WHERE
    strftime('%s', c_date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)

Using the strftime() function, we can output the difference between two datetime from the current time to a specified date time.

SELECT strftime('%s','now') - strftime('%s','2019-09-05 03:52:35');

Output:

Output of strftime('%s','now') - strftime('%s','2019-09-05 03:52:35')
-----------------------------------------------------------
2010032

Convert DateTime to Hours, Minutes, Seconds, and Milliseconds

We use the strftime() function to convert the DateTime unit to the hours, minutes, seconds, and milliseconds. We need to use the strftime() function to achieve the following results.

SELECT strftime('%H %M %S %s','now');

Output:

Output of strftime('%H %M %S %s','now')
-----------------------------
16 54 04 2541572981

Calculate the Seconds From a Specified Time Until the Current Time (Now)

We also use the strftime() function to convert the DateTime data type to seconds and compute the total seconds remaining from the provided arguments to the current time in seconds.

SELECT strftime('%s','now') - strftime('%s','2019-01-04 01:25:47');

Output:

Output of strftime('%s','now') - strftime('%s','2019-01-04 01:25:47')
-----------------------------------------------------------
3221184

Calculate the Time History in Years, Months, and Days

Take a simple example of job maintenance history where there is a table with the job start to end date, and the user wants to calculate the total time history in terms of years, months, and days.

Here is the table format with three columns: emp_id, start_job_date and end_job_date. The data type of emp_id is integer while the rest of the columns, start_job_date and end_job_date, are DateTime.

To calculate the difference between the start_job_date and end_job_date from the above table using the SQL queries, we use the strftime() function to directly have the difference in years, months, and days.

SELECT strftime('%Y',end_job_date - start_job_date) as "Difference in years",
strftime('%m',end_job_date - start_job_date) as "Difference in months",
strftime('%d',end_date - start_job_date) as "Difference in days"
FROM job_history;

Output: