![]() You can easily modify this solution to display it in some other format. ![]() The solution presented here returns the last column as text. To do this, use the CONCAT() function in the outer query: You only need the integer part from this (i.e., without the decimal part), so use the FLOOR() function like this:įinally, you simply need to display in one string what you've calculated. For example, to find out how many minutes should be displayed, take minutes_part and divide it by 60, since there are 60 minutes in an hour. To get the number of seconds, minutes, hours, and days, divide the number of seconds in the remainder by the corresponding number of seconds in days, hours, or minutes. Once these remainders are calculated (in the second CTE, named differences), you can finally get the difference in days, hours, minutes, and seconds. Similarly, there are 3600 * 24 seconds in a day, so to calculate how many seconds there are in hours_part, write: For example, an hour has 3600 seconds, so to find how many seconds there are in minutes_part, find the remainder from the division by 3600 like this: Calculate how many seconds there are in excess of whole minutes ( seconds_part) to be used later to calculate the seconds, how many seconds there are in excess of whole hours ( minutes_part) to be used later to calculate the minutes, and how many seconds there are in excess of whole hours ( hours_part) to be used later to calculate the hours. TIMESTAMPDIFF(SECOND, departure, arrival) AS secondsįirst, calculate the difference between the timestamps in seconds, using the TIMESTAMPDIFF() function (the first CTE, named difference_in_seconds), just as in Solution 1. Solution 2 (difference in days, hours, minutes, and seconds): The end and the start arguments are the ending timestamp and the starting timestamp, respectively (here, departure and arrival, respectively). You have two timestamp values: timestamp1 and timestamp2 and want to find the difference between the two. To get the difference in minutes, choose MINUTE for the difference in hours, choose HOUR, etc. To get the difference in seconds as we have done here, choose SECOND. The unit argument can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. TIMESTAMPDIFF(SECOND, departure, arrival) AS difference Solution 1 (difference in days, hours, minutes, or seconds): You'd like to calculate the difference between the arrival and the departure. In the travel table, there are three columns: id, departure, and arrival. Timestamp and you want to calculate the difference between them.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |