I cant query a column that is Datetime using the year, month and seconds

Question

My site was built with WordPress and I have the following case: The table I want to query is the wp_posts and the column to be targeted is the post_date which is of the time Datetime (not Date and I cant change it because of WordPress's convenience).

How want to fire a sql query that will give me all the details about an event posted on a specific date that will come from a calendar.

I tried this:

$date = new DateTime(2019-07-03);

$date_format = $date->format("Y-m-d");

$request = $pdo->query("SELECT * FROM posts WHERE post_date = ".$date_format);

$result = $request->fetch()

If I var_dump() the $dateformat I can see the data, however, my $result shows nothing. I then concluded that it will never show anything because in table, the dates also have times.

So, how can I get those details if I only have year, month, day, and not the hours?

If you are using wordpress, to get the post you can use wp_query https://developer.wordpress.org/reference/classes/wp_query/ — Shivendra Singh, Jul 12 '19 at 11:57

Vincent Decaux · Answer 1 · 2019-07-12T11:56:37.670

3

Dates are considerated as string in SQL. You must use quotes :

$request = $pdo->query('SELECT * FROM posts WHERE post_date = "'.$date_format .'"');

By the way, yes if you have datetime columns, you need to query in other way, using BETWEEN or simply :

$date_format_next = $date->add(1, 'day')->format('Y-m-d');
$request = $pdo->query('SELECT * FROM posts 
                WHERE post_date >= "'.$date_format .'"' 
                  AND post_date < "'.$date_format_next .'"');

edited Jul 12 '19 at 11:56

answered Jul 12 '19 at 11:51

Vincent Decaux

9,857
6
56
84

As mentioned before, this wont work because we are using date format in year, month and day, when in the database the records are store with currentTimeStamp – Sidney Sousa Jul 12 '19 at 11:56
I just posted an answer to my own question and hope you can let me know if perhaps that is not a good practice. I will still up vote yours though because it looks logical as well, though longer. – Sidney Sousa Jul 12 '19 at 12:00
It's not a good practice, LIKE query will take more time. Look here if you are interested in performances issues : https://stackoverflow.com/questions/1754411/how-to-select-date-from-datetime-column/30378572#30378572 – Vincent Decaux Jul 12 '19 at 12:04
There is just a syntax error in your answer that am struggling to figure out. Problem a quote missing somewhere. But I already marked the answer as useful – Sidney Sousa Jul 12 '19 at 12:14

score 0 · Answer 2 · answered Jul 12 '19 at 11:59

0

After browsing through w3schools I ended up concluding that I can use the sql search.

SELECT * FROM `wp_posts` WHERE `post_date` LIKE "%2019-07-03%"

My query works now

answered Jul 12 '19 at 11:59

Sidney Sousa

3,378
11
48
99

score 0 · Answer 3 · answered Jul 12 '19 at 12:03

0

Your column post_date is a datetime field and I found the requested query has only the date value so you can use this query as:

$request =$pdo->query("SELECT * FROM posts WHERE DATE(post_date) = '$date_format'");

answered Jul 12 '19 at 12:03

rosy_acube

31
4

I cant query a column that is Datetime using the year, month and seconds

3 Answers3