SQLite Date and Time

In the world of database management, understanding how to handle dates and times is important. SQLite is a popular relational database management system that offers a robust set of functions and features for working with date and time data. From storing timestamps to performing date calculations, SQLite provides several functions for developers and database administrators. In this article, we will learn about date and time functions along with various SQLite in-built functions with the help of examples and so on.

What are the Date and Time Functions in SQLite?

SQLite does not have any dedicated date and time data types, as we have in other database management systems. But its built-in date and time functions use other existing data types such as TEXT, INTEGER, etc. Date and time are useful when we want to store the exact entry time or in the calculation of duration etc. It can also help in performing data analysis-related tasks. We will see various Data and Time functions in SQLite which are mentioned below:-

• date()
  
• time()
  
• datetime()
  
• julianday()
  
• strftime()
  
• timediff()

Example of SQLite Date and Time

To understand Date and Time in SQLite, we need a table on which we will perform various operations and queries. Here we will consider a table called geeksforgeeks which contains(id, name, questions_solved, submission_time, and submission_date  as Columns.

After inserting some records into the geeksforgeeks , the table looks:

Explanation: In the above image, we can clearly observe that all the records have there respective submission date and time. As we have inserted all the records in one date but at different times. Therefore all the records have the same date but different time stamps.

Example 1: Using datetime() Function

In this example, we are going to demonstrate the use of datetime(). This method will get us both i.e. date and time. By default, it will get the current UTC date and time. To get the local date and time, we have to specify it in the parameter.

CASE 1: datetime() without specifying local time.

In this case, we will see how datetime() will work, if we do not specify local date and time in the parameters.

Query:

SELECT datetime('now') as date_with_time;

Output:

Explanation : In the above image, the date and time is mentioned is UTC date and time.

Note: UTC is short form for coordinated universal time. It is used to regulate world clock.

CASE 2: Specifying local time in datetime()

In this case, we will specify 'localtime' inside the datetime() method. This will help us to get our current date and time of our location.

Query:

SELECT datetime('now','localtime') as local_date_with_time;

Output:

Explanation : In this image, the date and time displayed is our local date and time (time at which we have run this query).

Example 2: Using date() Function

In the previous example, we had seen how to get both date and time at once. In this example, we will explore, how we can get only date.

Query:

SELECT date('now', 'localtime') as current_date;

Output:

Explanation: In the above image, we can see a date has been displayed. This is the date at which query had been executed. As we have specified 'localtime' in the parameters, it will return as our local date.

Example 3: Using time() Function

Just like in the previous example, we are going to explore how we can fetch current time.

Query:

SELECT time('now', 'localtime') as current_time;

Output:

Explanation: In the above image, we can clearly notice, a time has been displayed. This is our local time at which we had executed our query. Similar to the previous example, we have specified here 'localtime' too. This will return us our local time.

Example 4: Julian Date & Time - julianday()

In this example, we are going to implement julianday() in SQLite. This function will return us the Julian day number. The Julian day is the continuous count of the day since the beginning of the Julian period ( January 1, 4713 BC ).

Query

SELECT julianday('now', 'localtime') as julian_day;

Output:

Explanation: In the above image, a date has been shown, which is not in human readable form. This is actually the Julian date.

Example 5: Using strftime() Function

In this example, we are going to demonstrate the use of strftime() function. This function is used to customize how date and time values are represented. It is used for standard time format.

Query

SELECT strftime('%d-%m-%Y', 'now', 'localtime') as local_date;

Output:

Explanation: We have specified the format inside the parameters of the strftime() function. We have specified to keep day at first, followed by month and then year.

• %d: represents the day of the month.
  
• %m : represents the month.
  
• %Y : represents the year.

Example 6: Using timediff() Function

In this example, we are heading to demonstrate the use of timediff(). This function is generally used to calculate the difference in the time.

Query:

SELECT timediff(datetime('now') , datetime('now', 'localtime')) as local_date;

Output:

Explanation: This function helps us to calculate the date and time difference. In this example, we have calculated the time difference between UTC date and time with our local date and time. Negative and positive sign denotes how much a particular time is behind or ahead of other mentioned time.

Conclusion

Overall, date and time function is used to store exact entry time for an record or while performing data analysis related tasks and computing time difference. Nevertheless, SQLite do not have any dedicated date type to store date and time, we can use INTEGER, TEXT etc to store there values. We have covered its various functions like date(), time(), datetime() etc. with there respective examples. Now you have a good knowledge of SQLite's date and time functions with different scenarios. Now you can perform date and time related queries with ease and get the desired output.