ADD_TIME using INTERVAL

[aikorei]

For some reason, this isn't working as I'd expect. The current value I'm trying to change starts as:

 

2013-02-13 16:30:00

 

I then use this query:

 

SELECT DATE_ADD(`end`, INTERVAL 30 MINUTE) FROM `my_table` WHERE `ID` = 3

 

And the result becomes:

 

2013-02-13 17:30:00

 

Now if I didn't know any better, it looks like 60 minutes was added, not 30.

 

When I then try using INTERVAL 2 HOUR, the result comes out as:

 

2013-02-13 19:00:00

 

It also seems to me that the result is 1.5 hours later, not two.

 

What am I not getting about this??

Edited February 19, 2013 by aikorei

[jazzman1]

Hey, nice smile

 

I do not see the date-time string in your DATE_ADD function.

[aikorei]

Hey, nice smile

 

Thanks.

 

I do not see the date-time string in your DATE_ADD function.

 

I tried passing the name of the column ('end') in as the first argument for DATE_ADD since that's the information I'm trying to alter. I'm not sure if this is the right way to go about it or not, but it does seem to be changing the value that's contained there where I expect it to.

 

I'm pretty much brand new at most of this, so if there's a better way I'm all ears.

 

And thanks for the response. =)

[jazzman1]

No, I'm not an expert too (our master could be say later ) but......according the manual you need to pass a proper date-time string like that:

 

SELECT DATE_ADD('2006-07-00 12:00:09', INTERVAL 1 DAY)

[kicken]

Verify that your data is what you think it is.  Your example data works just fine for me, and I see no reason why it wouldn't:

http://sqlfiddle.com/#!2/96453/1/0

 

 

[requinix]

It also seems to me that the result is 1.5 hours later, not two.

2.5 hours. Both of them are 30 minutes beyond what you expected.

 

Where are you seeing the original and result values? And a totally random question that will probably lead nowhere, what timezone are you and your server in?

[edit] And on that note, what happens if you select `end` without any DATE_ADDing?

Edited February 19, 2013 by requinix

[aikorei]

Where are you seeing the original and result values? And a totally random question that will probably lead nowhere, what timezone are you and your server in?

[edit] And on that note, what happens if you select `end` without any DATE_ADDing?

 

I'm working in phpMyAdmin, directly with the DB, watching the values change by using queries in phpMyAdmin. I'm in Seattle (PST), though I'm not sure where the servers are, to be honest.

 

When I run this simple query:

 

SELECT end FROM `my_table` WHERE `ID` = 3

 

current result is:

 

2013-02-13 17:00:00

 

(I've been tinkering a bit, trying to figure out what's happening, so it's not the same as when I posted above)

[kicken]

SELECT `end`, DATE_ADD(`end`, INTERVAL 30 MINUTE) FROM `my_table` WHERE `ID` = 3

 

That will show you the original value and the modified value side-by-side so you can do an effective comparison.

 

[aikorei]

I used that query three times and got the following results:

 

SELECT `end`, DATE_ADD(`end`, INTERVAL 30 MINUTE) FROM `oyuvfn_app_appointments` WHERE `ID` = 3


2013-02-13 17:00:00 2013-02-13 17:30:00




SELECT `end`, DATE_ADD(`end`, INTERVAL 20 MINUTE) FROM `oyuvfn_app_appointments` WHERE `ID` = 3


2013-02-13 17:00:00 2013-02-13 17:20:00




SELECT `end`, DATE_ADD(`end`, INTERVAL 2 HOUR) FROM `oyuvfn_app_appointments` WHERE `ID` = 3


2013-02-13 17:00:00 2013-02-13 19:00:00

 

It seems as though the new value is being calculated correctly, but it's not replacing the old value. All of those values populated 17:00:00 as the start value...?

 

Edit: Perhaps I was only assuming DATE_ADD actually updates the cell...do I need to actually use UPDATE here? Also, this still doesn't explain what was happening earlier on. That was just weird (though I'm will to admit there may have been some operator error in there somewhere).

Edited February 19, 2013 by aikorei

[kicken]

A SELECT doesn't change the values in a row, only reads them.  To modify the value you need to issue an UPDATE statement.

[aikorei]

A SELECT doesn't change the values in a row, only reads them. To modify the value you need to issue an UPDATE statement.

 

Ah. I'm guessing that's my problem.

 

Me <-- noob

 

Thanks.