Mysql Select datetime, greater than timestamp

Question

I'm trying to add a WHERE clause to my SELECT to get dates from my table WHERE the datetime in the table is greater than a passed in time stamp.

SELECT * FROM record WHERE UNIX_TIMESTAMP(REPLACE(date_value, 'T', '')) >= 1388552400;

Here is a sample of the data

+--------+------------------------------+
| id     | date_value                   |
+--------+------------------------------+
| 344380 | 2014-12-01T00:00:00          |
| 344381 | 2014-12-23T00:00:00          |
| 344382 | 2014-12-16T00:00:00          |
| 344383 | 2014-12-24T00:00:00          |
| 344384 | 2014-12-17T00:00:00          | 
+--------+------------------------------+

I've tried a few things, but I either get an empty set or a waring of Incorrect datetime.

jaynp · Accepted Answer · 2015-01-05 00:13:05Z

3

You need to replace the T in your date_values with a space, ' ' not an empty string, ''.

So the following query should work as expected:

SELECT * FROM record WHERE UNIX_TIMESTAMP(REPLACE(date_value, 'T', ' ')) >= 1388552400;

To debug your original query, I ran the following:

SELECT id, UNIX_TIMESTAMP(REPLACE(date_value, 'T', '')) FROM record;

which returned timestamps of all 0.

So then I followed with:

SELECT id, REPLACE(date_value, 'T', '') FROM record;

where I was able to see the missing space in your date_value.

http://sqlfiddle.com/#!2/a184f/6

edited Jan 5, 2015 at 0:13

answered Jan 4, 2015 at 23:58

jaynp

3,3354 gold badges35 silver badges45 bronze badges

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

1 Comment

Dandrews Over a year ago

Ugh staring at that for an hour and didn't even realize it needed to be a space. Thanks!

Collectives™ on Stack Overflow

Mysql Select datetime, greater than timestamp

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