Connect and share knowledge within a single location that is structured and easy to search. Here, you can get hour of the day as below. SELECT * FROM messages WHERE time > UNIX_TIMESTAMP (NOW ()) - 604800. I'll try using TIMESTAMPDIFF(seconds) and ssee if that fixes things. Note: You need to pass two date / datetime values along with the unit (eg, day, month, etc. that you wish to see in the format you want. I am not sure whether there is any inbuilt function for that or not, i think it can be done by applying simple mathematics. MySQL DATEDIFF: Calculate Days Between Two Dates. The function counts whole elapsed units based on UTC with a DAY being 86400 seconds. Here is an example that uses date functions. 000000) seconds + (minutes+(hours+((days+(months*30)+(years*365))*24))*60 )*60:I have a MySQL table with two datetime columns. MySQL TIMESTAMPDIFF() Function - The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. 25 < ?'I'm trying to add seconds to a date based on certain events that occur. Before MySQL 5. TIMESTAMPDIFF() function MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another. TIMESTAMPDIFF () does not support dynamic units like that. Your first answer. If you want the difference between just two times like '11:10:00' minus '10:20:00' then use select. UNIX_TIMESTAMP () : This function in MySQL helps to return a Unix timestamp. SyntaxSubtracting timestamps in MySQL. We're using SQL Server 2008 R2 and the conversion failed when time is more than 24:. As an example, if we entirely ignore any "seconds" portion of the datetime/timestamps, and work with whole minutes, something like this: NOTE: this is not fully tested. If you wanted to avoid SEC_TO_TIME (seconds), you can build up the string yourself. They return the minute or second part of the value. MySQL convert timediff output to day, hour, minute, second format Ask Question Asked 12 years, 8 months ago Modified 4 years, 2 months ago Viewed 59k. The SEC_TO_TIME () function converts numeric seconds into a time value (in format HH:MM:SS). g. Using TIMESTAMPDIFF : NOTE:- Following Assumptions are made for TIMESTAMPDIFF () function. start,c1. answered Feb 4, 2018 at 5:33. 11. so. In MariaDB, you can use TIMESTAMPDIFF function. createDate), AVG(TIMESTAMPDIFF(SECOND, tbl. 3 is really old -- you really should update to a more recent version : You'll get more support ; Lots of bug fixes; New features and enhancements; And, probably, better performancesThat's because from_unixtime (emphasis mine):. Stack Overflow. The latter is longer, but in terms of cpu time should be faster. MySQL SUBTIME () subtracts one datetime value from another. SELECT TIMESTAMPDIFF (SECOND, start_time, end_time). TIMESTAMPDIFF. walter. Syntax : TIMESTAMPDIFF(unit,expr1,expr2). select count (session_id), client_session_id. So, your query should be: select email, createddate, lastloggedin, datediff (minute, createddate, lastloggedin) from udb. 注意. 045 enddate = 2010-02-23 03:45:39. The schema is SYSIBM. e. MySQL 計算兩個日期時間的間隔 TIMESTAMPDIFF() MySQL 可以用 TIMESTAMPDIFF() 函數來相減兩個 datetime 或 date。 TIMESTAMPDIFF() 語法 (Syntax) TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) TIMESTAMPDIFF() 會返回 datetime_expr2 − datetime_expr1 相減後的整數，其中 unit 表示整數的單位要是什麼。DATEDIFF: . 私の実行系では TO_SECONDS() と同じ結果が得られました; Conclusion. TIMESTAMPDIFF in a php foreach-loop. Description. If you divide until day, you are fine (every single day every year has the same amount of seconds). 2 MySQL datediff strange results. . Simple but elegant. Since UNIX-timestamps are the number of seconds passed since 1970-01-01 00:00:00, you'd have to calculate using seconds. Just make the second argument '2015-01-01' and as long as your EndDate is good, it should work. g. DATEDIFF takes only two arguments in MySQL. 1 Answer. MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。Adds the integer expression interval to the date or datetime expression datetime_expr. DATEADD. Follow. Since the question is being tagged for mysql, I have the following implementation that works for me and I hope similar alternatives would be there for other RDBMS's. TIMESTAMPDIFF(unit, timepoint1, timepoint2): returns the number of time units (SECOND, MINUTE, HOUR, DAY, MONTH or YEAR) between timepoint1 and timepoint2 . NOTE the most voted up answer in this chain is INCORRECT! Using HOUR will only return hours as an integer. Jan 18, 2022 at 12:05. For example, if you wanted to calculate the difference between two timestamps in seconds, you would use the following query: SELECT TIMESTAMPDIFF (SECOND, start_timestamp. Otherwise I will have to code that in PHP. As a result, such columns use DATETIME display format, have the same range of values, and there is no. user1864610. At least these are real and true seconds, unlike the MySQL DATEDIFF where they are. date_or_time_part must be one of the values listed in Supported Date. Apr 4, 2018 at 6:36. TIMESTAMPDIFF giving unexpected result. Follow. imei as imei, f. 0. Ask Question Asked 9 years ago. MySQL TIMEDIFF() Function MySQL Functions. You should use TIMESTAMPDIFF () to achieve that. Get your own SQL server SQL Statement: Edit the SQL Statement, and click "Run SQL" to see the result. In general, it is required that the types of all the columns are in agreement. A date or date with time is returned. I have my SQL statement like this trying to get the difference in 2 timestamps greater than 10 minutes. If I change the second column statement (the one on the 8th) to TIMESTAMPDIFF(SECOND, "2021-11-08 01:59:59-04:00", "2021-11-08 01:00:01-06:00") (only changing the offset) then MySQL's answer changes to 3602. TIMESTAMPDIFF( HOUR , now( ) , FROM_UNIXTIME( 1364814799 ) ) will return negative and positive values, if you need to use x>this_timestamp. The. SELECT UNIX_TIMESTAMP (timestring) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD. INTERVAL allows either YEAR and MONTH to be mixed together or DAY, HOUR, MINUTE and SECOND. n (Connector/NET 8. If the second date or time is earlier than the first date or time, the result is negative. Only show hours in MYSQL DATEDIFF. 097332 trigger_time = 2021-10-10 14:11:13. 13. You can see this with e. The difference is 25 (hours). In Sybase ASE you can use DATEDIFF function to get the difference between two datetime. – Ergest Basha. Most of the date/time functions in. MySQL TIMESTAMPADD () adds time value with a date or datetime value. @Enrico - Not true. Some days have an extra second or two seconds depending on the year. Sorted by: 18. Definition and Usage. seconds, minutes, hours, etc. TIMESTAMPDIFF is from mysql db. DATE_ADD, DATE_SUB, TIMESTAMPADD. Improve this answer. Method . You specify the unit to add, as well as how many of that unit to add. walter. 4. Павел П. Date values are interpreted as DateTime with the time part set to 00:00:00 by default. elapse)/60 as diff from( SELECT c1. I tried this: SELECT DATE_SUB (NOW (), upload_date) FROM is_meeting_files. The TIMESTAMPDIFF () function will then return the difference in the unit specified. SELECT TIMESTAMPDIFF(HOUR, '2010-11-29 13:13:55', '2010-11-29 13:16:55') as newtable; | newtable | 7 1 row in set (0. A record is inserted for each print and contains the store ID and timeStamp. An expression that returns a value that is a built-in SMALLINT or INTEGER data type. MySQL中两个Timestamp之间的秒差 在MySQL中，可以使用TIMESTAMPDIFF函数来计算两个Timestamp之间相差的秒数。 SELECT TIMESTAMPDIFF(SECOND,'2021-09-01 10:00:00', '2021-09-01 10:01:30') as total_seconds; 上述语句的执行结果为90，即两个Timestamp相差90秒。1901 to 2155. . First we calculate the absolute difference in seconds and put that in a variable; then we check if we get one or more of each (day hour minute), and if so we calculate how any we get and put that in a variable; for units after the largest, we do the same, but first we subtract the seconds allotted to the previous unit from our overall. Here is my trigger: SET NEW. 647 seconds. The function subtracts one datetime value from the other in the specified unit. ), the start timestamp, and the end timestamp. Im not sure if using "AS thisisit" is a current function of TIMESTAMPDIFF but I. But if I put DAY OR MONTH it works I really can’t figure out what might be the . mysql> SELECT TIMESTAMPDIFF (MINUTE,'2003-02-01','2003-05-01 12:05:55'); -> 128885. I have a column where I log all time entries of any event. TIMESTAMPDIFF () in MySQL returns a value after dividing one DateTime expression by another. To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF (unit, start, end) function. numeric-expression. 1、此函数的参数具有混合类型，比如 begin 是DATE值，end 可以是 datetime 值。. SELECT timestampdiff (minute,created_at,now ()) AS TIMEDIFF Below is the TIMEDIFF in minutes using the above query id TIMEDIFF. end_date - INTERVAL (q. The function returns the result of subtracting the second argument from the third argument. sql. If you wanted to avoid SEC_TO_TIME (seconds), you can build up the string yourself. TimeStamp1, t1. TIMESTAMPDIFF not working on mysql query in codeigniter. After that you just select Hours, minutes, and seconds from that. I was wondering how - is done between two timestamps and what the unit is. You can use this to get integer value. In this case, we compare two datetime values (as opposed to just the date values as in the previous examples). maxDt)) AS Duration FROM (SELECT DATE(i. The main problem is as follows: In order to find the difference between days you need to use datediff(); datediff() returns the difference in days. If TIMESTAMPDIFF () returns NULL, we can find rough age by subtracting the current year from the year of birth. 1. The function uses conditional statements to perform the appropriate conversion. to seconds Share. 例如， '2020-01-01 01:01:01' 和 '2020-01_01 01:01' 被视为有效的日期和时间值。. CURRENT_TIMESTAMP: returns the current SQL timestamp (UTC) . In this case MySQL ignores that index: example (check how many fields are processed when you are use DATE_ADD on the field and how many fields are processed when you are use DATE_SUB on the constant (like NOW()). 0. This implies that the difference between times should not be more then 30 seconds. Mysql Timediff function is not working for me for long date. Tham số unit được sử dụng để định nghĩa đơn vị của giá trị được trả về. INT TIMESTAMPDIFF(unit, DATETIME datetime_expr1, DATETIME datetime_expr2) Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. MySQL - Comparing Two Negative Time Values. The date is in CURRENT TIME STAMP format, just to clear that up. – Mehran. minDt, tbl. +1 for to the point the stored timestamp is less than x minutes. DATE_SUB () Subtract a time value (interval) from a date. To calculate the difference between two date/datetime expressions, use TIMESTAMPDIFF. email FROM cb_sessions LEFT JOIN (cb_user. Clearly your system or JVM is not configured to use UTC time. PHP MySQL TIMESTAMPDIFF with seconds not working. Conclusion: In this blog, we explored several strategies to convert seconds to days,. 212k 129 690 1515. I ran this on a shared server from the ISP JustHost, MYSQL version 5. Q&A for work. If you want to have the difference between two DATETIME values, use TIMESTAMPDIFF:. 5 日付の計算. when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it. The timestamp difference returns the difference between two dates in seconds. seconds, minutes, hours, etc. If you are using timestamp, this could be the solution in MySQL using SQL. Another approach to convert TIMESTAMP to UNIX time involves utilizing the TIMESTAMPDIFF() function in combination with UNIX_TIMESTAMP(). 返回值. For example, if you wanted to calculate the difference between two timestamps in seconds, you would use the following query: SELECT TIMESTAMPDIFF (SECOND, start_timestamp. g. TIMESTAMPDIFF in a php foreach-loop. This will provide you a whole number: SELECT TIMESTAMPDIFF (WEEK, date1, date2) AS weeks; To include a fraction for days, use: SELECT TIMESTAMPDIFF (DAY, date1, date2) / 7 AS weeks_days; or a fraction for seconds, use: SELECT TIMESTAMPDIFF (SECOND, date1, date2) / 604800 AS weeks_secs; as 604800 is 7 * 24 * 60 * 60; MySQL 将timediff输出转换为天、小时、分钟、秒的格式 在MySQL中，timediff函数用于计算两个时间之间的差异。当我们使用timediff函数后，它将返回一个时间差，并以时、分、秒格式表示。 TIMESTAMPADD(HOUR, TIMESTAMPDIFF(HOUR,CURDATE(),timestamp_column_name), CURDATE()) Instead of CURDATE() you can use an arbitrary date, for example '2000-01-01' Not sure if there could be problems using CURDATE() if the system date changes between the two calls to the function, don't know if Mysql would call both at the same time. TIMESTAMPDIFF. mysql timediff no proper output when endtime is 24hrs. Behavior Type. payment_time = 2021-10-29 07:06:32. I want to create one new generated column that contains time difference of the two datetime columns. 00000 is clearly outside the supported range of 00:00:00 -. What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on. Apparently the UTC_TIMESTAMP (). The unit for the result (an integer) is given by the unit argument. answered Oct 2, 2017 at. TIMESTAMPDIFF excludes the start date in its calculation. . 58 and found no overflow with timestamps differing by up to at least 10000 years. One alternative would be to define a function (MySQL stored program) that will calculate that difference. This is the query I am working on right [email protected], NOW()). In MySQL, you would do something like this to get the time interval:. Reading time: 2 minutes. SELECT id, Present, Date, Time, SEC_TO_TIME (TIMESTAMPDIFF. The Mysql SEC_TO_TIME function is used to create time values based on a given second. TIMESTAMPDIFF not working on mysql query in codeigniter. Hisham. Stack Overflow. This function in MySQL is used to return a value after subtracting a DateTime expression from another. 0. 引数は、結果を表す単位と、差異を取る 2. TIMESTAMPDIFF giving unexpected result. time_start) / 60 as diff_minutes. This is the syntax: select TIMEDIFF('2020/01/30 T00:00:00. I had a following part of SELECT query calculating different between the UTC timestamp in rcv. select id, CAST (datediff (curdate (),birth_date) / 365 as int) from student. Recommended MySQL Tutorials. TIMEDIFF('00:04:50','23:59:59');. The above works as expected. "timestamp" is a column in MYSQL which I hold a timstamp as such. . As the previous example demonstrates, the TIMESTAMPDIFF () allows you to specify a unit for the results to be returned as (in fact, it requires you to specify the unit). What I have written is : date_diff ('minute',payment_time,trigger_time) <= 15 I basically want the count of users who paid within 15 mins of the triggered time thus I. SELECT * FROM table WHERE TIMESTAMPDIFF (MINUTE,timestamp,NOW ()) AS thisisit. You need to use the function available in your db in the below queryset. g. Weeks, quarters, and years follow from that. MICROSEGUNDO,. MySQL Date Functions. The function subtracts one datetime value from the other in the specified unit. For example, if we want to know the difference between two dates in seconds: SELECT id, job, TIMESTAMPDIFF(SECOND, start_date, end_date) AS runtime FROM example_table; This calculates end_date - start_date in. Note: Since the the Unix Epoch date is based on UTC, when your MySQL server uses a timezone with an offset, using FROM_UNIXTIME(seconds - seconds_from_epoch), the resulting datetime will be off by that offset. Learn more about TeamsReturns. The TIMESTAMPDIFF() function is truly a powerful tool in our SQL arsenal, enabling us to easily calculate differences between two timestamps. TimeStamp2, t2. Connect and share knowledge within a single location that is structured and easy to search. DATEDIFF() seconds with millisecond to float or decimal. So I used TIMESTAMPDIFF in seconds. The TIMESTAMPDIFF() function in MySQL is used to subtract a period of time between two datetime values. To calculate the difference between two dates in seconds, we can make use of this function along with the UNIX_TIMESTAMP function. 11. TIME_START returns an interval which can be converted to seconds using extract () and those can be converted to minutes: extract (epoch from now () - a. Return the current time. 21. 1 Answer. so, your second date parameter subtracting from first parameter it return you 3. MySQL only supports microsecond for a higher resolution, but you can achieve it by converting microsecond to millisecond manually: SELECT TIMESTAMPDIFF (MICROSECOND, now (3), updated_at) / 1000 AS diff_in_ms FROM events; Share. MySQL is quite different from SQLite. minutes = total_seconds DIV 60. My guess - he used EXTRACT (SECONDS FROM. All you need is to execute the code below and then use the function. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. TIMESTAMPADD () Examples – MySQL. I have tried the following query but it only returns the difference in seconds from the first row. See it on sqlfiddle. Result is expressed as a time value (and it has the limitations of the time data. , day, month, etc). If your data is stored in the table in one time zone (i. Some days have an extra second or two seconds depending on the year. How can i store the return value from the timestampdiff function. types. Select UNIX_TIMESTAMP (last_attack) as my_required from users. Starting with your example query, something like this would probably work: SELECT foo FROM table t LEFT JOIN frequencies f USING (frequency_id) WHERE MOD ( (CASE WHEN f. 1. The argument order and syntax is also different. How to Create and Drop Mysql Database using Command Line and PHP Script. The schema is SYSIBM. 01 sec) Share. Definition:– SUBTIME() function is built-in MySQL function, which is commonly used to subtract a time value from a time or datetime value or exp. SELECT datedifference (date1,. Timestampdiff () function takes three arguments. The MySQL TIMESTAMPDIFF() function is used to find the difference between two date or datetime expressions. 0 datediff() not ignoring time. Share. 32. Yes, because the timestamp handles the leap years. I take it that you are using MySQL. Follow. 5 日付の計算. Take a look at the code below - notice the 1 millisecond difference in the two returned values. 0. It will return the difference in terms of an INTERVAL DAY TO SECOND type: org. SELECT TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27') AS 'Difference in Minutes'; Result: +-----+ | Difference in Minutes | +-----+ | 15 |. 단순히 일 차이를 가져올 때 사용하는 것이 datediff 함수입니다. mysql. In MySQL SUBTIME() function tutorial, we would love to share with MySQL SUBTIME() function with its syntax, definition, parameters, and several examples. Then user 10 logged in at 2021-09-03 18:49:00. SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-10 23:59:59'; SELECT DATEDIFF(@date1, @date2) AS 'DATEDIFF', TIMESTAMPDIFF(day, @date1,. This function takes three arguments: the unit of time you want to measure the difference in (e. By default, this is set to 8. Another solution I found was using the MySQL timestampdiff method, still too generic, but I wouldn't have to worry about possible Time-Zone differences. To avoid having to repeat yourself, you can wrap the calculation: SELECT HOUR(x. It's a powerful tool for performing date and time calculations, allowing you to manipulate temporal data in various ways. I have a problem with my query , where by even if I post now the post ,the TIMESTAMPDIFF Does not work with MINUTE . To determine how many years old each of your pets is, use the TIMESTAMPDIFF () function. 21. It effectively calculates the difference in seconds and divides (discarding the fractional part) by the number of seconds in the chosen unit. Practice. Connect and share knowledge within a single location that is structured and easy to search. MySQL uses the TIMESTAMPDIFF function to calculate the difference between two dates or datetimes: SELECT TIMESTAMPDIFF(unit, startdate, enddate) FROM table_name; Where unit represents the unit of time, like YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. Select timestampdiff(SECONDS, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference; The above query statement will return the different between the two datetime. Like for example the conversion value to Hours and Minutes is 4 Hours and 30 Minutes. id_feature = f. Seconds, Minutes, Hours, Days, Weeks,. Share. 1. 3. MySQL :: MySQL 5. createDate) maxDt FROM Impressions i INNER JOIN carts c ON (i. timestamp_end), TIME (a. I) if you need to calculate the elapsed time in seconds between two timestamp columns try this: SELECT extract ( day from (end_timestamp - start_timestamp) )*86400 + extract ( hour from (end_timestamp - start_timestamp) )*3600 + extract ( minute from (end_timestamp - start_timestamp) )*60 + extract ( second from (end_timestamp - start. 000000) if empty ,TimeStampDiff(SECOND, Current Date, Current Timestamp) As SecondIt seems there were some bugs with that function, on old versions of MySQL 5. datetime) - JulianDay(students. SET @date1 = '2010-10-11 00:00:00', @date2 =. @Bruno - If the data types are TIMESTAMP WITH TIME ZONE, the interval that results from subtracting the two should take care of any time zone/ daylight savings time conversion. PHP MySQL TIMESTAMPDIFF with seconds not working. Use this link to know how to get accurate result using EXTRACT () and JULIAN_DAY () function. How can i store the return value from the timestampdiff function. TIMESTAMPDIFF ¶ Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part. is_deleted IS NULL AND r. +1 For keeping the query sargable and not wrapping the timestamp column within a function for comparison. g. SQL SELECT DATEDIFF (end_time, start_time) as `difference` FROM timeattendance WHERE timeattendance_id = '1484' start_time = 2012-01-01 12:00:00 end_time = 2012-01-02 13:00:00. my approach : set unit as SECOND and then use SEC_TO_TIME. jstu. The MySQL TIMESTAMPDIFF on the other hand can do a datetime second comparison: SELECT TIMESTAMPDIFF(SECOND,'2013-01-14 10:59:10', '2013-01-17 11:50:34'); # returns 262284 The result, 262284, is the real seconds between the two dates. In lack of something better to use for a date SQL. But the output I get is 1 (day). The TIMESTAMPDIFF function returns an estimated number of intervals of the type that is defined by the first argument, based on the difference between two timestamps. When used as a number, a timestamp like '2021-01-02 03:04:05' will be treated as 20210102030405. 返回：end - begin 的时间差，并以 unit 指定的单位进行输出。. session_id =. Please follow up in a Java newsgroup. Possible duplicate of Only show hours in MYSQL DATEDIFF – Sebastian Brosch. 6. speed as speed FROM stops f, stops f2 where f2. Seconds (the absolute value of the duration must be less than 680105031408. ) to use for determining the difference. On the other hand, DATEDIFF () doesn’t allow you to specify a unit. Returns datetime_expr2 - datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. Syntax :. SELECT current_timestamp() => 2018-01-18 12:05:34 which can be converted to seconds timestamp as. The function supports units of years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds, and nanoseconds. Here’s an example: SELECT TIMESTAMPDIFF(unit, datetime1, datetime2) AS difference FROM your_table;Caused by: java. MySQL. I am using MySQL. To understand the MySQL convert timediff output to day, hour, minute, and second format, you need to use CONCAT () from MySQL. MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates. , begin is a DATE value and end is a DATETIME value. Requires 3 arguments. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. MySQL MySQLi Database. TIMESTAMPDIFF (unit,datetime_expr1,datetime_expr2) where unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. And most of the time, this will require calculating the number of days between date values. Share. Look at the documentation for TIMESTAMPDIFF () TIMESTAMPDIFF (unit,datetime_expr1,datetime_expr2) Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. runTime,NOW()) > 20. On the other hand, DATEDIFF () doesn’t allow you to specify a unit. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. . SELECT TIMESTAMPDIFF (<INTERVAL>,<timestampFrom>,<timestampTo>); If you want to find the difference between the first and the last timestamp of a given host ID, here you are: SELECT TIMESTAMPDIFF (MINUTE,MIN (pingtime),MAX (pingtime)) FROM yourTable WHERE. The second parameter of DATEDIFF takes a datetime so your string is implicitly converted to a datetime. Mysql TIMESTAMPDIFF for time datatype return negative value. MySQL provides a set of functioMySql version >=5. SELECT SUM(Seconds_In_State) From (SELECT Time_Stamp, MState, (-1 * TIMESTAMPDIFF(Minute, LEAD(Time_Stamp) OVER(ORDER BY Time_Stamp), Time_Stamp)) AS Seconds_In_State FROM Mstate WHERE. 6 Answers Sorted by: 11 You could use the native TIMESTAMPDIFF function : SELECT TIMESTAMPDIFF (<INTERVAL>,<timestampFrom>,<timestampTo>); If you want to find the difference between the first and the last timestamp of a given host ID, here you are: SELECT TIMESTAMPDIFF (MINUTE,MIN (pingtime),MAX (pingtime)) FROM yourTable WHERE host = 2; Share Fractional seconds for TIME , DATETIME, and TIMESTAMP values are supported, with up to microsecond precision. You would sum up the differences between the timestamps, then use that value (would be in milliseconds) to get the time: SELECT SEC_TO_TIME (time_milis / 1000) FROM ( SELECT SUM (UNIX_TIMESTAMP (date1) - UNIX_TIMESTAMP (date2)) as time_milis FROM table ). Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. Instead, the result is 838:59:59, which makes sense because that is the limit.