CODEWITHSUNDEEP
phpmysqldatetime
Talon
Talon

Reputation: 4995

MySQL Select if Timestamp is in the past

I have a database like this:

TABLE
Name               |            timestamp
------------------------------------------
Hello              |            2013-02-23 14:26:18
Yo                 |            2013-03-22 14:26:18

I want to do a SQL Select where only things that are in the past (e.g. the timestamp isn't set for the future) are selected.

What would be the best way to do that?

Thanks for any help.

EDIT:

I tried the suggestions but none of them seem to work, here is the query:

SELECT * FROM TABLE WHERE timestamp<NOW()

And it still selected everything, I also tried:

SELECT * FROM TABLE WHERE timestamp<CURRENT_TIMESTAMP

And it still selected everything again.

It's really wierd, when I use "timestamp>CURRENT_TIMESTAMP" it seems to work (it hides all items not from the future) but not when I try the other direction which makes no sense. Could it be a timezone issue or something like that?

Upvotes: 6

Views: 3323

Answers (5)

Keith John Hutchison
Keith John Hutchison

Reputation: 5287

select * from table where `timestamp` < now()

Upvotes: 1

Bjoern
Bjoern

Reputation: 16314

Have you tried

SELECT * 
FROM yourtable T
WHERE T.timestamp < NOW();

...?

NOW() ...

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

Upvotes: 1

BenM
BenM

Reputation: 53246

SELECT `Name` FROM `TABLE` WHERE `timestamp` < NOW();

Should get you what you want.

Upvotes: 5

jazzytomato
jazzytomato

Reputation: 7239

Try the following query :

SELECT * FROM YourTable WHERE 'timestamp' < CURRENT_TIMESTAMP

Upvotes: 1

Burkhard
Burkhard

Reputation: 14738

SELECT * FROM TABLE WHERE timestamp<now();

Upvotes: 1

Related Questions