How to select a php date() format in SQL

Question

I have an SQL table with a field "time_created" in the form "Wed, 19 Aug 2015 03:58:00 -0600". I need to check this field against today and perform a where statement according to it, as in

SELECT * FROM table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time_created) >= 3600*24*30

to select records where time_created is older then a month.

Problem is UNIX_TIMESTAMP doesn't understand formats as "Wed, 19 Aug 2015 03:58:00 -0600". Any other way?

Do you mean that it isn't an actual date but just a text in English? What role does PHP play in the question? — Álvaro González
– Álvaro González, Commented Sep 13, 2015 at 15:33

Phil Cross · Accepted Answer · 2015-09-13 14:55:51Z

1

Try:

SELECT *
FROM table 
WHERE DATE(NOW()) > DATE_SUB(STR_TO_DATE(time_created, '%a, %e %b %Y %T'), INTERVAL 1 MONTH)

This will find all records where the current date (DATE(NOW())) is bigger than time_created subtract ` month.

answered Sep 13, 2015 at 14:55

Phil Cross

9,33212 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivo Over a year ago

thanks. Just a small change. It works with > DATE_ADD

Collectives™ on Stack Overflow

How to select a php date() format in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related