CODEWITHSUNDEEP
mysqldatetime
сами J.D.
сами J.D.

Reputation: 513

DateTime operation with MySQL

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)?

Upvotes: 1

Views: 461

Answers (3)

Christopher Gamella
Christopher Gamella

Reputation: 21

    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;

Upvotes: 1

Traveller
Traveller

Reputation: 397

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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) = ...

Upvotes: 2

Related Questions