Back to list SQLite How to Calculate the Difference Between Two Timestamps in SQLite Database: SQLite Operators:JULIANDAY() Problem: You have two columns of the type timestamp and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1 (difference in days): SELECT id, departure, arrival, JULIANDAY(arrival) - JULIANDAY(departure) AS difference FROM travel; The result is: iddeparturearrivaldifference 12018-03-25 12:00:002018-04-05 07:30:0010.8125 22019-09-12 15:50:002019-10-23 10:30:3040.778125000186265 32018-07-14 16:15:002018-07-14 20:40:300.18437499972060323 42018-01-05 08:35:002019-01-08 14:00:00368.2256944444962 Discussion: To calculate the difference between the timestamps in SQLite, use the JULIANDAY() function for both timestamps, then subtract one from the other. This way, you get the difference in days. The integer part of the difference is the number of full days, and the decimal part represents a partial day. The JULIANDAY() function returns the number of days since noon Greenwich Mean Time on November 24, 4714 B.C. You can read more about this function in the documentation. Solution 2 (difference in seconds): SELECT id, departure, arrival, ROUND((JULIANDAY(arrival) - JULIANDAY(departure)) * 86400) AS difference FROM travel; The result is: iddeparturearrivaldifference 12018-03-25 12:00:002018-04-05 07:30:00934200 22019-09-12 15:50:002019-10-23 10:30:303523230 32018-07-14 16:15:002018-07-14 20:40:3015930 42018-01-05 08:35:002019-01-08 14:00:0031814700 Discussion: If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400, or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute. Since the decimal parts for difference in days aren't 100% accurate due to rounding errors, you may get a non-zero decimal part. Rounding the result to the nearest integer, using the ROUND() function, fixes the problem. Similarly, you could calculate the difference in minutes or in hours. Just change * 86400 to * 3600 (minutes) or * 60 (hours). Recommended courses: SQL Basics Standard SQL Functions Data Types in SQL Recommended articles: How to Analyze a Time Series in SQL Performing Calculations on Date- and Time-Related Values What Is Vertabelo’s SQL Cheat Sheet? See also: How to Add a Month to a Date in SQLite How to Get the Current Date in SQLite How to Get Yesterday’s Date in SQLite How to Format a Datetime in SQLite How to Calculate the Difference Between Two Dates in SQLite Tags: SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.