CODEWITHSUNDEEP
phpmysqldatabase
denikov
denikov

Reputation: 869

MySQL select query, concat and convert to date two fields and compare to time intervals

I have two STRING fields in my table which make up the date and time. I want to concat those fields, convert them into a date so I can get the values between a chosen time. I think I'm running into a problem with str_to_date format inside my query. Here's what I have in the table

theDate        theTime
Mon, Jan 20    7:00 pm
Mon, Jan 20    9:00 pm
Tue, Jan 21    5:00 pm

The PHP:

date_default_timezone_set('EST');
//trying to set up the same format as the concatenated fields will be
$now = date('D, M j g:i a');
include('..//db_connect.php');
try {
    $stmt = $conn->query("SELECT * FROM table1 WHERE STR_TO_DATE(CONCAT(theDate, ' ', theTime), '%a, %b %e %l:%i %p') 
        BETWEEN (STR_TO_DATE('$now', '%a, %b %e %l:%i %p') - INTERVAL 4 HOUR AND STR_TO_DATE('$now', '%a, %b %e %l:%i %p') - INTERVAL 10 HOUR)");
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($results);
} catch (Exception $e) {
    echo $stmt."<br>".$e->getMessage();
}

What I get is syntax error near 'HOURS AND STR_TO_DATE('Wed, Jan 21 7:07', '%a, %b %e %l %i') - INTERVAL 10 HOURS' at line 2. What's wrong with the query? Also, is there a way to print out the query itself, to see the concatenated values, to see the formatted values?

Upvotes: 0

Views: 1191

Answers (4)

Alexandru Olaru
Alexandru Olaru

Reputation: 7112

I guess your error is that you are using HOURS instead of HOUR, but I suggest a better solution to use date_sub() and mysql now() methods into something like:

SELECT * FROM table1 WHERE myDate BETWEEN date_sub(now(), INTERVAL 4 HOUR) AND date_sub(now(), INTERVAL 10 HOUR);

An other sugestion is to save datetime fields as datetime and not as string less work after.

Upvotes: 0

Javier C. H.
Javier C. H.

Reputation: 2123

I think you will need to pass in the year somewhere... also your are missing the colon : on the STR_TO_DATE. It's HOUR in singular Try like this:

// Assuming $now is in the format '2014 Wed, Jan 21 7:07'
SELECT STR_TO_DATE('$now', '%Y %a, %b %e %l:%i') - INTERVAL 4 HOUR;

As per the second part of the question, I think you could do a separated query like this:

SELECT STR_TO_DATE('$now', '%Y %a, %b %e %l:%i') - INTERVAL 4 HOUR, STR_TO_DATE('$now', '%a, %b %e %l:%i %p') - INTERVAL 10 HOUR;

And then show the results, to see what are the actual values being looked up.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

This is your query:

SELECT *
FROM table1
WHERE STR_TO_DATE(CONCAT(theDate, ' ', theTime), '%a, %b %e %l %i %p') 
        BETWEEN (STR_TO_DATE('$now', '%a, %b %e %l %i %p') - INTERVAL 4 HOURS AND
                 STR_TO_DATE('$now', '%a, %b %e %l %i %p') - INTERVAL 10 HOURS)

This doesn't quite look like MySQL syntax. There are several small problems -- the hours and the extra parentheses around the BETWEEN, the BETWEEN valus in the wrong order. Perhaps this does what you want?

SELECT *
FROM table1
WHERE STR_TO_DATE(CONCAT(theDate, ' ', theTime), '%a, %b %e %l %i %p')
          BETWEEN date_sub(now(), interval 10 hours) AND
                  date_sub(now(), interval 4 hours);

You should use native types for dates and times in the database, rather than storing them as strings. If they are strings, don't give them names like "date" and "time" that suggests that they are something they are not.

Upvotes: 0

Sandro Eric
Sandro Eric

Reputation: 342

One thing is wrong is HOURS. Must be HOUR

Upvotes: 1

Related Questions