Timestamps in MySql and Daylight Saving Time [dst]

Question

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"

why are you using a unix timestamp in the first place? why not just compare to a string date? — John Ruddell, Sep 01 '14 at 16:10
`I can't compare strings directly cause I also like to get results from a certain "range" like "All birthdays from August to October"` you can do that with comparing dates... instead of unix timestamp — John Ruddell, Sep 01 '14 at 16:13
yes.. it is mysql's date functions in the docs... you can use BETWEEN as well i'll edit my answer — John Ruddell, Sep 01 '14 at 16:18
theres a few links you can look at http://stackoverflow.com/questions/11827178/how-to-get-date-between-two-dates-in-mysql --- or this one http://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql — John Ruddell, Sep 01 '14 at 16:22

John Ruddell · Accepted Answer · 2014-09-01T16:25:49.493

2

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.. :)

edited Sep 01 '14 at 16:25

answered Sep 01 '14 at 16:18

John Ruddell

25,283
6
57
86

Actually, I'm not convinced that the str_to_date function is actually required here. With a yyyy-mm-dd date format, I can't think of any values that would give you the wrong answer if you just used the string value of meta_value. – Dan Bracuk Sep 01 '14 at 16:27
@DanBracuk that would be true... but I would prefer to let MySQL compare it as a date that way i'm sure. with other date formats it could easily cause problems but might not with this one – John Ruddell Sep 01 '14 at 16:29
1

While it's purer to convert to a date first, it's faster to not do so. – Dan Bracuk Sep 01 '14 at 16:34
@DanBracuk true.. it wouldn't be a lot faster as mysql handles dates pretty well.. unless there was an index on the longtext column. regardless the OP can see from the comments and adjust accordingly if preffered :) – John Ruddell Sep 01 '14 at 16:40
The date format is always YYYY-MM-DD but the field type has to be longtext. I know it would be much easier with a date field but this is not possible – Xaver Sep 01 '14 at 16:47

Timestamps in MySql and Daylight Saving Time [dst]

1 Answers1