Mysql timestampdiff seconds. 0. Mysql timestampdiff seconds

 
 0Mysql timestampdiff seconds 549345 (and then false

MySQL には、年齢の計算や日付の一部の抽出など、日付の計算に使用できる関数がいくつか用意されています。. All this data is stored in a single table. This function only works correctly at the level of seconds for timestamps within a few days of each other; it overflows gracelessly (as I discovered with great pain). 1. The TIMESTAMPDIFF function returns the. Discussion: To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF (unit, start, end) function. One expression may be a date and the other a datetime. I want to make a function call that hase efect in SQLite like TIMEDIFF in MySQL. Just make the second argument '2015-01-01' and as long as your EndDate is good, it should work. orders. 1 why does mysql timediff function give wrong output? Load 7 more related questions Show. The timestamp difference returns the difference between two dates in seconds. You need to use the function available in your db in the below queryset. DATE () Extract the date part of a date or datetime expression. One month is considered elapsed when the calendar month has increased and the calendar day and time is equal or greater to the start. status_timestamp) FROM t tsub WHERE tsub. so actually you are doing. 11. Note that unit values can be different in SQL Server DATEDIFF and MariaDB TIMESTAMPDIFF. i. 4, the instances are limited in which a fractional seconds part is permitted in temporal values. SELECT id, Present, Date, Time, SEC_TO_TIME (TIMESTAMPDIFF. If thats. timestamp off by minutes. values('id', 'diff_time')MySQL TIMESTAMPDIFF() function explained. . transaction_pk and tsub. Here are the first 25 rows of. NOTE the most voted up answer in this chain is INCORRECT! Using HOUR will only return hours as an integer. Switch between time and CURRENT_TIMESTAMP, you will get a positive number. 1. Month-difference between any given two dates: Have a look at the TIMESTAMPDIFF () function in MySQL. 0. 4. 1、此函数的参数具有混合类型,比如 begin 是DATE值,end 可以是 datetime 值。. Requires 3 arguments. timeDiff), SECOND(x. MySQL SUBTIME () subtracts one datetime value from another. +1 For keeping the query sargable and not wrapping the timestamp column within a function for comparison. If your data is stored in the table in one time zone (i. 5 hours). Timestampdiff () function takes three arguments. jstu. Here is an example that uses date functions. Param1, t2. Otherwise I will have to code that in PHP. Method . The difference between each reading value is 300 seconds but this may vary slightly so i need to calculate the difference between each timestamp and then add the difference in readingvalue for that. transaction_pk = tmain. On the other hand, DATEDIFF () doesn’t allow you to specify a unit. Practice. I have tried to used timestampdiff, but it takes three arguments. +1 for to the point the stored timestamp is less than x minutes. SELECT TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27') AS 'Difference in Minutes'; Result: +-----+ | Difference in Minutes | +-----+ | 15 |. So basically what this query does is that it calculates days by deducing dates in the first line. /format. For example: Check in date is 12-04-2010 Check out date 15-04-2010 The day difference would be 3. But when I try to save the output "TIMEDIFF" into a temporary table as Datatype Integer/datetime/time it saves as a different value. It is mainly used to calculate the date and time values. This function in MySQL is used to return a value after subtracting a DateTime expression from another. Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value. Solution is using select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00'); (note the opposite. timestamp_end), TIME (a. Simple but elegant. I would like to be able to calculate difference. e . But the output I get is 1 (day). DATEDIFF in Aurora MySQL only calculates differences in days. After that you just select Hours, minutes, and seconds from that. 3. The TIMESTAMPDIFF () function will then return the difference in the unit specified. MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates. This is the very lengthy MySQL Date and Time manual page. Requires 3 arguments. You can use the TIMESTAMPDIFF() function in MySQL. -- Display TimeStampDiff in truncated Seconds and Microseconds-- TimeStampDiff will set the time to midnight (00:00:00. Subtracts the 2nd argument from the 1st (date1 − date2). By default, this is set to 8. For example: select col1, avg (timestampdiff (SECOND, startTimestamp, endTimestamp)) as avgdiff from table group by col1. The following query selects all rows with a date_col value from within the last 30 days: . Teams. But your problem is in Java, not MySQL. So now my question: I thought apache drill replaces the function "TIMSTAMPDIFF" at runtime. end, TIMESTAMPDIFF(MINUTE,c1. TIMESTAMPDIFF. 01. I have tried the following query but it only returns the difference in seconds from the first row. To calculate the difference between two date/datetime expressions, use TIMESTAMPDIFF. Some days have an extra second or two seconds depending on the year. The. runTime,NOW()) > 20. When you insert a TIMESTAMP value into a table, MySQL. 0), 1) AS "Worked Hours" FROM `db_foo` WHERE matriculation='X' AND date='2017-yy-yy'; which would return. Example 3 – A ‘datetime’ Example. Here, First, we are finding the. In the select clause, the TIMESTAMPDIFF (SECOND, NOW (),q. Then you can convert seconds to minutes, or days, or months, or etc within your case statement, depending where they fall in the range. TIMESTAMPDIFF () does not support dynamic units like that. 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. It is a bad idea to use some operations on the index field. datetime) - JulianDay(students. Instead, the result is 838:59:59, which makes sense because that is the limit. The basic syntax: TIMESTAMPDIFF(unit,datetime1,datetime2); You can find a list with different types of units, check out the list in the section above. Actually the data type for the columns is TIME, and this a sample of data stored: 07:11:40 07:11:56. 0. You may convert operands to TIMESTAMP, substract (obtaining the difference in seconds) and convert to hours. SELECT f. 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. The function counts whole elapsed units based on UTC with a DAY being 86400 seconds. How can i store the return value from the timestampdiff function. timestampdiff ():根据指定单位返回两个时间相减的时间差。. select t. 1. 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 *. 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. timeDiff), SECOND(x. In applications that involve event scheduling, DATE_ADD () helps determine future event dates based on specified intervals. 引数は、結果を表す単位と、差異を取る 2. 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. The following table summarizes the difference between these two functions: TIMEDIFF () TIMESTAMPDIFF () Requires 2 arguments. 返回:end - begin 的时间差,并以 unit 指定的单位进行输出。. Syntax :. The following table, based on the MySQL manual, describes the format specifiers:. If the second date or time is earlier than the first date or time, the result is negative. Let us create a table. If you want to find the date/time difference between the first and the last timestamp of each ticket, there are several functions that can help you: select ticket_id, timediff (max (timestamp), min (timestamp)) as diff, timestampdiff (hour, min (timestamp), max (timestamp)) as diff_in_hours, timestampdiff (minute, min (timestamp), max. I currently developed a cron that runs every 1 minute to analyze the last 60 seconds of the bot records in database, I need to group the conversation ID's that have more than 3 records within 60 seconds in the same url and client_session_id. TIMESTAMPDIFF ( numeric-expression string-expression. UNIT can be SECOND MINUTE HOUR DAY WEEK. 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. TIMESTAMPDIFF(MINUTE, TIMESTAMPADD(HOUR, TIMESTAMPDIFF(HOUR, @from, @to), @from), @to); -- 22 -- and the same for the seconds SELECT. MySQL where datediff equals to some value. Clearly it is paying attention to the offset, and in this situation, it is using it correctly. As an entirely different approach, you could get the difference in seconds, and convert to TIME datatype. MySQL Clauses DISTINCT, WHERE, ORDER BY, GROUP BY, HAVING. answered Oct 2, 2017 at. slaakso. Reading time: 2 minutes. Aarthy. SELECT datedifference (date1,. 2, “Connector/NET Versions and Entity Framework Core Support” )I want to get the difference of a date and a datetime and display it as elapse time, example: 4days 7hr 8min 3sec. mysql timediff no proper output when endtime is 24hrs. 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. CURRENT_TIMESTAMP returns the current datetime something like 2017-03-09 15:19:53. createDate), AVG(TIMESTAMPDIFF(SECOND, tbl. Subtracts the 2nd argument from the 3rd (date2 − date1). SELECT TIMEDIFF (end_time,start_time) AS "total" FROM `metrics`; meaning 116 hours, 12 minutes and 10 seconds. Note: time1 and time2 should be in the same format, and the. From the msdn, it returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. You can write your query like this: SELECT * FROM eventList WHERE date BETWEEN UNIX_TIMESTAMP ('2013/03/26') AND UNIX_TIMESTAMP ('2013/03/27 23:59:59'); When you don't specify the time, MySQL will assume 00:00:00 as the time for the given date. Another approach would be (assuming you sort out the above issue) to store the time intervals as seconds using TIMESTAMPDIFF(): UPDATE my_table SET time_spent=TIMESTAMPDIFF(start, end)); SELECT SEC_TO_TIME(SUM(time_spent)). If you have a variable holding another UNIX_TIMESTAMP, you can get the difference and turn seconds to minutes (and round/truncate the result if needed): SELECT ROUND ( (UNIX_TIMESTAMP ()-1506947452) / 60, 0) Share. MySQL convert timediff output to day, hour, minute, second format. ), the start timestamp, and the end timestamp. Use mysql's timestampdiff () function to calculate the difference and use from_unixtime () function to convert the timestamp into a datetime data type. It only returns the result in days. The default timezone used by MySQL is the SYSTEM timezone. The value is expected to be the result of subtracting two timestamps and converting the result to CHAR. 1. . ). The unit for the result (an integer) is given by the unit argument. Hot Network Questions Job offer doesn't smell quite right - is this a scam?. Syntax: TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);. PHP MySQL TIMESTAMPDIFF with seconds not working. threads-1)) SECOND)) <= 0 is quite confusing without. However, the search condition TIMESTAMPDIFF (SECOND, NOW (), (q. 2 Answers. The function counts whole elapsed units based on UTC with a DAY being 86400 seconds. 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. The second parameter of DATEDIFF takes a datetime so your string is implicitly converted to a datetime. 58 and found no overflow with timestamps differing by up to at least 10000 years. 6 Reference Manual. TIMESTAMPDIFF ¶ Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part. SELECT start, end, TIMESTAMPDIFF(HOUR, `start`, `end`) FROM table1 WHERE start >="2018-01-01" AND ende <= "2018-12-31. TRUNC(TIMESTAMPDIFF(seconds,min(BE. Unable to use 'where' when using 'timestampdiff' in mySQL. If this mode is enabled at the time that a table is created, TIMESTAMP columns are created as DATETIME columns. g. One may be a date and another is datetime. . frequency. Calculating the difference in seconds of DateTime values: To calculate the difference, just change the argument MINUTE to SECOND. How to Create and Drop Mysql Database using Command Line and PHP Script. W3Schools offers free online tutorials, references and exercises in all the major languages of the web. DATE_SUB () Subtract a time value (interval) from a date. I have a problem with my query , where by even if I post now the post ,the TIMESTAMPDIFF Does not work with MINUTE . Note: You need to pass two date / datetime. You can then use SEC_TO_TIME (seconds) to get the format hh:mm:ss, and take the right 5 characters if you really need hh:mm. select count (session_id), client_session_id. A BIGINT. TimeStamp1, t1. 0. Return values from temporal functions include fractional seconds as appropriate. So the TIMESTAMPDIFF(MINUTE. 2. UNIX_TIMESTAMP () : This function in MySQL helps to return a Unix timestamp. MySQL has a handy function TIMESTAMPDIFF which can calculate the difference between two timestamps in a variety of units, including seconds. If start is greater than end the result is negative. About;. If start is greater than end the result is negative. 2. This is the query I am working on right [email protected], NOW()). Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that. If your subtraction involves daylight savings change-overs, a particular. ), the start timestamp, and the end timestamp. Share. Follow answered Jun 7, 2018 at 11:23. The MySQL TIMESTAMPDIFF() function is used to find the difference between two date or datetime expressions. 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. I ran this on a shared server from the ISP JustHost, MYSQL version 5. mysql> SELECT something FROM tbl_name-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;. It returns zero if it a date, NULL if not. To determine how many years old each of your pets is, use the TIMESTAMPDIFF () function. This function takes three arguments: the unit of time you want to measure the difference in (e. 7 Date and Time Functions. 549345 (and then false. (Consider handling of open/close that span a daylight savings time change. answered Feb 4, 2018 at 5:33. 3 Answers. 6. In order to convert days to a date, so you can get the number of years etc you need to use from_days(); from_days() doesn't really work before 1582, to quote from the documentation: "Use FROM_DAYS(). DateDiff to show Minutes and Seconds. I am using the below query to find the time difference in minutes. TiDB 会尽量与 MySQL 的行为保持一致,但可能无法在所有. On the other hand, DATEDIFF () doesn’t allow you to specify a unit. TIMESTAMPDIFF not working on mysql query in codeigniter. TIMESTAMPDIFF() function MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another. So subtracting a TIMESTAMP WITH TIME ZONE that is 5 am Eastern from a TIMESTAMP WITH TIME ZONE that is 2 am Pacific will result in an interval of 0. At least these are real and true seconds, unlike the MySQL DATEDIFF where they are. 1. id-1 and f. 01 sec) Share. 0 [Release 1. my approach : set unit as SECOND and then use SEC_TO_TIME. Months aren't an issue in this case since subtracting two timestamps just use days and seconds but 'days' can be a problem. timestampdiff() 语法 这是 mysql timestampdiff() 函数的语法:I want to update the datetime round to 15 minutes in a MYSQL database table. start, c1. MySQL timestampdiff() 函数就比 datediff() 功能强多了,datediff() 只能计算两个日期(date)之间相差的天数。Adds the integer expression interval to the date or datetime expression datetime_expr. 0. Example 1 – Basic Difference Here’s an example that demonstrates the basic difference between these functions. So we could modify the previous example so that TIMESTAMPDIFF. select SEC_TO_TIME (sum (diff)) as result from ( select timestampdiff (second,min (case when log_tpe='start_break' then timestamps end) , min (case when log_tpe='end_break' then timestamps end)) as diff from t group by date (timestamps),hour (timestamps) )A. 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. Get your own SQL server SQL Statement: Edit the SQL Statement, and click "Run SQL" to see the result. 3. 58 and found no overflow with timestamps differing by up to at least 10000 years. 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. Fractional seconds for TIME , DATETIME, and TIMESTAMP values are supported, with up to microsecond precision. mysql> create table DemoTable -> ( -> DueDatetime1 datetime, -> DueDatetime2 datetime -> ); Query OK, 0 rows affected (0. timestampdiff () requires valid dates for the second and third argument. Instead, the result is 838:59:59, which makes sense because that is the limit. Timestampdiff () function takes three arguments. when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it. In a Unicode database, if a supplied argument is a. 3. It only returns the result in days. Share. Use this version: CONCAT ( TIMESTAMPDIFF (day, TIME_START, TIME_END), 'd ', MOD (TIMESTAMPDIFF (hour, TIME_START, TIME_END), 24), 'h ', MOD (TIMESTAMPDIFF (minute, TIME_START,. 1. 1. types. The format of a TIMESTAMP is YYYY-MM-DD HH:MM:SS which is fixed at 19 characters. Conclusion: In this blog, we explored several strategies to convert seconds to days,. 0. 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 seconds. 1 Answer. After executing you can use it like this. m. 19-Aug-2022. Result is expressed as a time value (and it has the limitations of the time data. Mysql Timediff function is not working for me for long date. mmm". MySQL TIMESTAMPDIFF Function Example SELECT TIMESTAMPDIFF(SECOND,’2013-01-14 10:59:10′, ‘2013-01-17 11:50:34’); #262284. Recommended MySQL Tutorials. I have a table which contains 2 columns date_picked_begin and date_picked and there will be multiple rows which contain a date in each. timediff(`date2`, `date1`) which gives me the time difference but if the days are different it does not account for the day difference. Here is an example that uses date functions. 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. MySQL is quite different from SQLite. TIMESTAMPDIFF ( unit type, datetime expression1, datetime expression2); Unit is used to express the difference of datetime or date in days, months, etc. n (Connector/NET 8. arrival_time) # returns seconds as integer. select(sum(df. On : 11. For example if the result is 490 seconds, since it is greater than 59. If you wanted to avoid SEC_TO_TIME (seconds), you can build up the string yourself. 0. I have a table that contains among other things an id a time in the format date:time (YYYYhhmmss) and a readingvalue. TIMESTAMPDIFF. end_date) which calculates the remaining seconds is straightforward and easy to understand. Usage and definition for TIMESTAMPDIFF function (Doc ID 2756136. The syntax of sec_to_time() function is: SEC_TO_TIME(seconds); Hereseconds is the number of seconds you want to be. 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. I am using the DATEDIFF function to calculate the difference between my two timestamps. MySQLで利用できる日付関数について確認します。. 1. TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) Returns datetime_expr2 – datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. So you need to. id, f. seconds, minutes, hours, etc. is_ignored IS NULL AND r. Return the current time. I would recommend you to use the query like this: SELECT DATE(tbl. user where createddate >= '2019-09-01' and createddate <= '2019-09-30'. We can simply aggregate over each player and sum the diff of the starting and ending timetamps, using a CASE expression to replace the (somewhat odd) zero timestamp with the current. My SQL query is correct in answers alone but when it comes to the format it fails. Here’s that simple example we used earlier: SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); This piece of code gives us the difference in seconds between the two specified dates. – Ergest Basha. SELECT TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW()); If the server's timezone is PST this will return -8. Though, if you have an index on the timestamp column, this would be faster because it could utilize an index on the timestamp column if you have one: SELECT * FROM table WHERE. Here’s a quick rundown of the functions you can use in MySQL: ## #TIMESTAMPDIFF TIMESTAMPDIFF() returns the difference in the given unit. Below. The difference is 25 (hours). Syntax. The MySQL TIMESTAMP is a temporal data type that holds the combination of date and time. The unit for the interval as mentioned should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK,. First, it attempts to find a match for the %d format specifier, which is a day of the month (01…31), in the input string. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. I am using below code for today and database date. Share. Here is on way to solve it using window functions (available in MySQL 8. I take it that you are using MySQL. @Enrico - Not true. id = f. However, what I get is NULL, instead of the expected INT that represents seconds between two times. I have query in Mysql which return minutes using TIMESTAMPDIFF in table. 59) %s: Seconds (00. 如果任何一个参数为 NULL , TIMESTAMPADD () 函数将返回 NULL 。. when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it. my approach : set unit as SECOND and then use SEC_TO_TIME. Subtracts the 2nd argument from the 3rd (date2 − date1). About; Products For Teams; Stack Overflow. You should take a look the TIMESTAMPDIFF function. end_date - INTERVAL (q. Stack Overflow. An expression that returns a value that is a built-in. 2022/11/26. More information on time_format:. 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. Example: As an example, if timestamp A is 14:00 on Friday and timestamp B is 14:01 on Tuesday, the raw TIMESTAMPDIFF is. SELECT ABS (TIMESTAMPDIFF (SECOND, start_time, end_time)). – Ergest Basha. Improve this answer. 0. NET Framework that is supported by Connector/NET (see Table 7. Here's the sql:. Since you're working with a known set of units, you could use a CASE statement to achieve this. numeric-expression. answered Feb 4, 2018 at 5:33. timestamp 型のカラムを利用して日付の差分を取得したい場合は、そのまま減算しちゃダメ。 MySQL には各種日付用の関数があるので、適切なものを使いましょう。3 Answers. 21. 2. If you want the difference between just two times like '11:10:00' minus '10:20:00' then use select TIME_TO_SEC('11:10:00')-TIME_TO_SEC('10:20:00') Discussion: To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF (unit, start, end) function. In general, it is required that the types of all the columns are in agreement. Let us walk through them quickly: To calculate the difference between dates in days – SELECT DATEDIFF (`DATE-A`, `DATE-B`) FROM `TABLE`. The unit for the result (an integer) is given by the unit argument. By using the new function in 15. mmm". time_zone which I generally won't want/be able to change, and; The built-ins behavior around the time of DST changes (if global time zone uses DST) results in information loss in some use cases,To perform calculations between two dates or times in MySQL, you can use the DATEDIFF function or the TIMESTAMPDIFF function. After executing you can use it like this. , begin is a DATE value and end is a DATETIME value. 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. Those UNIX timestamps are stored as a number of seconds since 1970-01-01 00:00:00 UTC. The value returned is an INTEGER, the number of these intervals between the two timestamps. Select UNIX_TIMESTAMP (last_attack) as my_required from users. Use this version: CONCAT ( TIMESTAMPDIFF (day, TIME_START, TIME_END), 'd ', MOD (TIMESTAMPDIFF (hour, TIME_START, TIME_END), 24), 'h ', MOD (TIMESTAMPDIFF (minute, TIME_START, TIME_END), 60), 'm' ) AS. I would recommend instead converting the time differences to minutes and displaying the total number of hours as a decimal number, rather than a time: SELECT ROUND (SUM (TIMESTAMPDIFF (MINUTE, Initial, Final) / 60. I suggest you keep your difference in seconds, and when presenting it you adapt it as necessary. TIMESTAMPDIFF(MINUTE,T. Check the line when timestampdiff (minute, created_on, current_timestamp) > 3 AND < 60 then " minutes ago" to be correct you should change to when timestampdiff (minute, created_on, current_timestamp) > 3 AND timestampdiff (minute, created_on, current_timestamp) < 60 then " minutes ago". For EST using FROM_UNIXTIME will result. Because of two reasons: MySQL's built-in functions UNIX_TIMESTAMP and FROM_UNIXTIME take the time zone stored in @@global. If it returns 00:06:00 or 01:02:00, the corresponding record. 3.