• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 744
  • Last Modified:

MySQL subtract two DateTime fields and compare with # hours

I have two fields in DateTime format.
how can i find the difference in HOURS between the two fields.

i tried this example, which yields 0  (i would expect 2)

SELECT date_format('2011-03-02 12:00:00'-'2011-03-02 10:00:00',"%H")

thanks
0
willsherwood
Asked:
willsherwood
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
0
 
cyberkiwiCommented:
SELECT timediff('2011-03-02 12:00:00', '2011-03-02 10:00:00')
0
 
willsherwoodAuthor Commented:
that's it!  thanks
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
cyberkiwiCommented:
MySQL 5.5 and some earlier versions (maybe)

SELECT timestampdiff(hour, '2011-03-02 10:00:00', '2011-03-02 12:00:00')

Note: Later time at back
0
 
cyberkiwiCommented:
I contest marking the SQL Server answer as accepted for this MySQL question, it doesn't even work for MySQL!
0
 
willsherwoodAuthor Commented:
i'm sorry that you had this reaction.

indeed i saw that that answer was for SQL, but it gave me a lead which i looked up in MySQL for the equivalent, and adapted the concept (dropped the "hour" param), and got me to my next step.
I had not been aware of this vein of functions.
time_format(timediff('2011-03-02 12:00:00','2010-03-02 10:00:00'),"%H")

I shared the points at that moment in time, both answers were viable leads.In fact yours came in after i had already begun awarding points (i refreshed and re considered to share)

Request Attention if you feel strongly.   I need to get back to work, the night grows long.

0
 
cyberkiwiCommented:
willsherwood

It's less about the points than getting the solution database correct.  EE tries to be a solution database to any visitor, so someone could come along to look for your same problem, and attempt the "accepted answer" - only to find it does not work.
0
 
Aaron TomoskyTechnology ConsultantCommented:
I will agree with that last comment. A visitor to ee only sees the accepted solution. So mine should have been an assist and cyberkiwi should have given the answer just for this reason.
As for why I posted a mssql answer in a mysql zone, whoops. I answer a ton of questions in many zones and my bleary eyes thought it said mssql. I appologize for that, however it did get the author on the right path and to a solution.
0
 
South ModModeratorCommented:
Objecting - should have Accepted instead of allowing the 4-day autoclose, so please just close out this request.

SouthMod
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now