CODEWITHSUNDEEP
mysqlsql
Xaver
Xaver

Reputation: 11692

Timestamps in MySql and Daylight Saving Time [dst]

I have a table which holds different type of data. One of it are birthday dates:

ID | meta_key | meta_value
==========================
 1 | birthday | 2011-30-01
 2 | birthday | 2011-30-07
 3 | other    | not_related

With a known timestamp I would like to get all birthdays like

SELECT *
FROM table
WHERE meta_value = 'birthday' 
     AND UNIX_TIMESTAMP(STR_TO_DATE(meta_value,'%Y-%m-%d')) = 1296342000

which should return the first row (#1) but doesn't . The reason is the timestamp has to be 1296345600

I did a sqlfiddle to double check that.

It seems the DST is causing the issue but why? Are those UNIX time based timestamps not all UTC? I'll get my timestamp with PHP's strtotime() like

strtotime('2011-30-01')

Edit:

I can't compare strings directly cause I also like to get results from a certain "range" like "All birthdays from August to October"

Upvotes: 1

Views: 679

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

not sure why you need to convert it to a unix timestamp.. first thing the unix timestamp converts to a UTC time SEE DOCS

but it seems like you are trying to complicate things more than you should... you can just do a straight date comparison in mysql like this

SELECT * 
FROM meta
WHERE STR_TO_DATE(meta_value,'%Y-%m-%d') >= '2011-04-01';

you can also do multiple comparison 

SELECT * 
FROM meta
WHERE STR_TO_DATE(meta_value,'%Y-%m-%d') >= '2011-08-01'
  AND STR_TO_DATE(meta_value,'%Y-%m-%d') <= '2011-10-31';

Or you can use BETWEEN

SELECT * 
FROM meta
WHERE STR_TO_DATE(meta_value,'%Y-%m-%d') BETWEEN '2011-08-01' AND '2011-10-31';

just as a recommendation.. I would suggest you update the table and change the meta_value to an actual date instead of long text... its a good rule of thumb to store dates as their intended datatype.. :)

Upvotes: 2

Related Questions