CODEWITHSUNDEEP
mysql
Cavemanharris
Cavemanharris

Reputation: 183

Date and Time MySQL Statement

I have two fields, "cont_time_published" and "cont_date_published" that I want to use to filter results in a listing of records.

select *
from news
WHERE cont_date_published < CURDATE() AND cont_time_published < CURTIME()

I am trying to achieve listing that only shows records that the publish time and publish date is in the past. So that it would filter a record that has today's date but the time is still in the future.

Results are wrong when the date is today and the time is future.

cont_date_published is "DATE" only field and cont_time_published is "TIME" Field.

Upvotes: 0

Views: 56

Answers (4)

Fabio
Fabio

Reputation: 180

If you need to get the records published in the past (today in the past hours or before) try:

SELECT * 
 FROM news  
  WHERE cont_date_published < CURDATE() 
  OR (cont_date_published = CURDATE() AND cont_time_published < CURTIME());

sorry my mistake, forgot the OR

Upvotes: 2

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146573

You can CAST to an actual date:

WHERE CAST(CONCAT(cont_date_published, ' ', cont_time_published) AS DATETIME)<NOW()

... but this will possibly prevent query optimiser from using indexes (if any). An alternative would be:

WHERE cont_date_published<CURDATE() OR
    (cont_date_published=CURDATE() AND cont_time_published<CURTIME())

Of course, all this extra work could be easily avoided with a proper database design that makes use of a single DATETIME column:

WHERE cont_published<NOW()

Upvotes: 1

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3128

Try this

SELECT *
FROM news
WHERE (cont_date_published < CURDATE() ) AND ( cont_time_published < CURTIME() ) 
ORDER BY id DESC;

Upvotes: 1

Sree Hari
Sree Hari

Reputation: 107

   select * from news WHERE cont_date_published < CURDATE()

This will be enough to get the records that are Published in the Past.

Upvotes: 0

Related Questions