In relational databases, we deal with various data values with different data types. The date and time values can also be a part of the database. Sometimes, we need to deal with these values as well. In this article, we are going to study the calculation performed on date and time related values.
Let me explain this with an example. Assume we are dealing with a database of tutorial websites. So, it will take login details of users and store some information like total time of reading, exit time, etc. This will help to track the behaviour and the progress of the user. In this case, the database stores value in the form of time or date. The calculations should be performed on these values only. Then, you will get the total time of reading, accurate time of exit, average time spent on specific lessons, etc. Let’s see how to perform calculations on date-time-related values, but before that, we will see some basics.
Essential Date-Time Data Types
When working with date-time-related values, it’s crucial to understand the following data types:
1. Time
Time data type displays data values in hours, minutes, and seconds. The format is simple. The storage size of this data type is 3 to 5 bytes. Sometimes, nanoseconds are also displayed.
2. Date
Normally, the date is represented in the form of year, month, and day (YYYY-MM-DD). The storage size of this data type is 3 bytes.
3. DateTime
This data type displays both date and time. The date is represented as (YYYY-MM-DD), and the time as (hh:mm:ss). The storage size of this data type is 8 bytes. There is one more type to represent the datetime, that is datetime2. The only difference between these two data types is storage size. The storage size of the datetime2 data type is 6 to 8 bytes.
4. Interval
Interval data type helps to count a period between two dates, years, or two different time intervals. This data type can represent a period in two different formats. The first one is a year-month, and the second one is a day-time interval. The year-month data type will represent the period in the year-month format. The day-time will display in seconds, nanoseconds, days, hours, and minutes.
Arithmetic Calculations on Date-Time Related Values
We can perform different operations on date-time-related values. These are simple arithmetic operations. The output will be different depending on what we are using, for example, timestamp or interval. These operations make everything easy when someone deals with date-time-related values.
Subtracting Two Times, Intervals, and Timestamps
You can subtract two different timings (Time data) using the TIMEDIFF function. You can also use the normal way of subtracting the two timings. Using this function we can calculate the total time spent on a website. Let’s see both the execution with function and without function. I have implemented all the codes using an example table (Table 1).
Subtracting Two Timestamps (With Function)
SELECT
ts1,
ts2,
TIME_FORMAT(TIMEDIFF(ts1, ts2), '%H:%i:%s') AS TD
FROM Table1;Code language: SQL (Structured Query Language) (sql)
You can see in the above output that the Timestamps subtracted successfully.
Subtracting Two Timestamps (Without Function)
SELECT ts1, ts2, ts1 - ts2 AS TD
FROM Table1;Code language: SQL (Structured Query Language) (sql)
In both implementations, you can clearly see the output is correct. In this way, we can calculate the time difference between any two timings or intervals.
Subtracting Interval From Timestamp
One more method in which we can perform subtraction on time. We can subtract the time interval from the specific timestamp. This is very interesting. We are considering the interval as a day and hour. DATE_SUB function is used here.
SELECT
timestamp,
DATE_SUB(timestamp, INTERVAL 1 DAY) AS Output1,
DATE_SUB(timestamp, INTERVAL 2 HOUR) AS Output2
FROM Table2;Code language: SQL (Structured Query Language) (sql)In this example, I have used two different intervals, day and hour. Let’s see the results.
The operation is successfully performed. Let’s now try to do the addition operations.
Adding Two Times, Intervals, and Timestamps
We can add time and date data types using normal methods or functions. This way, we can solve various problems like calculating the total time spent on the website.
Adding Two Timestamps (With Function)
Adding two timestamps is as simple as adding two integers. We can use a simple ‘+’ operator. In the first method, we will use the TIMESTAMPADD function.
SELECT
ts1,
ts2,
TIMESTAMPADD(SECOND, TIMESTAMPDIFF(SECOND, ts1, ts2), ts1) AS Output
FROM Table3;Code language: SQL (Structured Query Language) (sql)
We have successfully added Timestamps.
Adding Two Timestamps (Without Function)
SELECT ts1, ts2, ts1 + ts2 AS Output
FROM Table4;Code language: SQL (Structured Query Language) (sql)
In the output, you can clearly see the results are correct!
Adding Timestamp And Interval
We can add timestamps and intervals using the TIMESTAMPADD function.
SELECT ts1, TIMESTAMPADD(MINUTE, 40, ts1) AS Output
FROM Table4;Code language: SQL (Structured Query Language) (sql)
Just like we subtracted the Interval from Timestamp we have added the Interval in Timestamp.
Other Operations On Date-Time Related Values
Other than arithmetic operations, we can perform various operations on date-time values. The date and time are present in almost every database of websites. There are different functions available to perform this operation. Sometimes, we need to find out the components of dates, times, etc. For example, month, year, second, hour, etc. In some cases, we need to format the date, and this is possible due to the DATE_FORMAT function. Let’s see these functions one by one.
Formatting Dates
We can format the dates using the DATE_FORMAT function. The date can be formatted in the year-month-day form.
SELECT DATE_FORMAT('2023-09-23', '%Y-%m-%d') AS Output;Code language: SQL (Structured Query Language) (sql)
Extracting Current Date-Time
To extract the current date and time, we can use the NOW() function.
SELECT NOW() AS Output;Code language: SQL (Structured Query Language) (sql)
Extracting Date-Time Components
We can also extract the date components from specific dates. Different functions are used to extract these components like YEAR, MONTH, DAY, HOURS, MINUTE, and SECOND.
SELECT YEAR('2023-09-23') AS Y, MONTH('2023-09-23') AS M, DAY('2023-09-23') AS D;Code language: SQL (Structured Query Language) (sql)
All these functions help to operate the time and date calculations. This is simple and easy.
Summary
This article will help you to understand the calculations performed on date and time values. Starting with the basics, date and time values have different data types. These data types are important to understand the methods. Some arithmetic operations like subtraction and addition can be performed using different operators, methods, etc. Other than this, some important functions required to perform the calculations are also explained in the implementation. Hope you will enjoy this article.
Reference
https://stackoverflow.com/questions/42602532/how-to-perform-arithmetic-function-in-date-time-in-sql