Mysql unix_timestamp with date_add does not give correct timestamp

Question

I am trying to a get a unix_timestamp after doing a date_add to my database field but it still giving me the unix_timestamp of the original value not after the date_add.

Here is my query.

SELECT data1.speed,UNIX_TIMESTAMP(Date_Add(data1.dateTimer , Interval '+08:00' hour_minute)) as dateTimer

For example the dateTimer value is 2015-10-02 15:27:53 after date_add is 2015-10-02 23:27:53 but the unix_timestamp is giving me 1443799673 which refers to 2015-10-02 15:27:53. I even tried the CONVERT_TZ function its still the same.

Did you try UNIX_TIMESTAMP(Date_Add(data1.dateTimer , Interval '+08:00' hour_minute)) and UNIX_TIMESTAMP(data1.dateTimer)? They should be different. — Hidde
– Hidde, Commented Oct 3, 2015 at 16:07
I have tried and both having same value. I read some other articles say there is a bug in mysql but mine is version: 5.1.73 — user5313398
– user5313398, Commented Oct 3, 2015 at 16:16
Why don't you try to create a UNIX_TIMESTAMP of the date value, and add 8 * 60 * 60 = 28800 seconds to the result, if it is really a bug? — Hidde
– Hidde, Commented Oct 3, 2015 at 16:20
Yes I am thinking on that line the problem the interval I keep in session value as string in this format +08:00 etc. So what is the best way to convert any of the timeZoneOffset into the hour? — user5313398
– user5313398, Commented Oct 3, 2015 at 16:22
You have two options: Parse the timezone string, and doing the calculations to get the number of seconds in the offset. You can also put the date into a DateTime object, and convert the timezone automatically by setting the default timezone and then changing the timezone of the object (see php.net/manual/en/class.datetimezone.php) — Hidde
– Hidde, Commented Oct 3, 2015 at 16:26

Hidde · Accepted Answer · 2015-10-04 08:39:07Z

1

My answer covers the case when the situation you describe is affected by a bug.

You have two options:

Parse the timezone information. Make sure it is always in the same format. For the case above (+08:00) you can use something like ($tz[0] == '+' ? 1 : (-1)) * (3600 * substr($tz, 1, 2) + 60 * substr($tz, 3, 2)). Here $tz is your timezone information.
Add this number of seconds to the timestamp of your data.
Parse the timestamp using a DateTime docs. This can be done when given a string of the format 2015-10-03 19:52:06. Set the timezone to your current timezone (docs). Then, modify the timezone with the users timezone (using setTimezone).

edited Oct 4, 2015 at 8:39

answered Oct 3, 2015 at 17:54

Hidde

12k8 gold badges46 silver badges71 bronze badges

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

2 Comments

user5313398 Over a year ago

Ok I take option one. Is this the first thing you do $tz="+08:00"? The rest I understand. I think the second option is more efficient but I dont know where to apply is it in the query itself or when I run my while loop ?

user5313398 Over a year ago

Yes that I have tested I dont know how to test your second method. Any further example how to put the tz value in it ?

Collectives™ on Stack Overflow

Mysql unix_timestamp with date_add does not give correct timestamp

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related