DateTime operation with MySQL

Question

I'm pretty bad with dates.

I have a mysql table with one field, which is OF DateTime type, called HoraRegistratBBDD.

What I want to do is to select data (any kind of data) from a specific day. So far I was doing this:

SELECT COUNT(*)
FROM
(
    SELECT mydata
    FROM mytable
    WHERE DATE(`HoraRegistratBBDD`) = '".$fecha."' AND
          FetOPerdutIMotiu = '1'
    GROUP BY Partit,
             mydata
) AS Col;

Where $fecha is something like "2016-09-03". THIS WORKS.

But I have a problem. When my HoraRegistratBBDD has (for example) this value:

2016-09-02 10:28:41

I would like to substract 15 hours from it. Meaning that I would like to treat this value like it's actually

2016-09-01 19:28:41

How can I do my query considering that I want to substract hours from it (therefore, day will change sometimes)?

Some Advice. Try to do some research before posting a question. I got 10 really good results from one single google search. One example: http://stackoverflow.com/questions/6020162/how-to-subtract-3-hours-from-a-datetime-in-mysql — Traveller, Oct 05 '16 at 08:23

score 2 · Answer 1 · answered Oct 05 '16 at 08:17

2

If you want to subtract 15 hours from the HoraRegistratBBDD column, then you can use DATE_SUB:

SELECT mydata FROM mytable
WHERE DATE_SUB(HoraRegistratBBDD, INTERVAL 15 HOUR) = ...

answered Oct 05 '16 at 08:17

Tim Biegeleisen

502,043
27
286
360

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

The function that you are looking for is DATE_SUB.

Here are a few links:

http://www.w3schools.com/sql/func_date_sub.asp

How to subtract 3 hours from a datetime in MySQL?

The first one shows you how it works and the other one is a similar question and it has been answered.

edited May 23 '17 at 10:34

Community

1
1

answered Oct 05 '16 at 08:20

Traveller

399
2
20

score 1 · Answer 3 · answered Oct 05 '16 at 08:30

    SELECT COUNT(*)
    FROM
    (
        SELECT mydata, DATE_FORMAT(HoraRegistratBBDD,'%Y-%m-%d') AS niceDate
        FROM mytable
        WHERE 
              FetOPerdutIMotiu = '1'
        HAVING niceDate = '".$fecha."'
        GROUP BY Partit,
                 mydata
    ) AS Col;

DateTime operation with MySQL

3 Answers3