Adding time to mysql timestamp column, with condition

Question

I have 3 time-related columns in my table:

vreme_od (TIME), vreme_do(TIME), datum(CURRENT TIMESTAMP)

vreme_od and vreme_do have H:i:s time values (the difference between vreme_od and vreme_do is 1 hour), vreme_do can be update from a script.

The thing is, I have to come up with a catch that updates vreme_do till a certain time, let's say, 21:00.

So, If you fire up the script at 20:53, for an example, vreme_od will have a value of "20:53:00", and vreme_do should have a value of "07:53:00" on the next day.

Can some one help me with this, I'm stuck for days with it...

http://stackoverflow.com/questions/1436827/adding-30-minutes-to-datetime-php-mysql — Jirka Kopřiva, Aug 29 '12 at 16:35
I've managed to that, but the thing is with transfering time to another day, on certain condition. — Marko Mijailovic, Aug 29 '12 at 16:54

score 2 · Accepted Answer · answered Aug 29 '12 at 17:23

2

Using CASE statement, (global) example for update:

UPDATE table
 SET `date` = CASE
              WHEN CURRENT_TIMESTAMP>'23:00:00' 
              THEN CURRENT_TIMESTAMP + INTERVAL 40 MINUTE
              ELSE CURRENT_TIMESTAMP END
 WHERE ...

Days will auto increment.

answered Aug 29 '12 at 17:23

Jirka Kopřiva

2,939
25
28

Thanks, I figured it out, it's a simple calculation, but I just didn't have inspiration... =) – Marko Mijailovic Aug 29 '12 at 20:03

score 1 · Answer 2 · answered Aug 29 '12 at 16:58

1

You can use AddTime Function to add some hour in the second column using MySql built in function.

answered Aug 29 '12 at 16:58

MD. Sahib Bin Mahboob

20,246
2
23
45

Could you explain that please? I forgot to mention that I already have a table with predefined "work hours" of days in the week. Once again, if the start time (vreme_od) is 2012-08-29 20:30:00, the end time (vreme_do) should be 2012-08-30 07:30:30, and not 2012-08-29 21:30:00. Thanks for your answer. – Marko Mijailovic Aug 29 '12 at 17:05
Can you please explain your question a bit? I got it kind of hard to understand your requirement properly – MD. Sahib Bin Mahboob Aug 29 '12 at 17:09
@user1481792 explain the columns briefly and add some example of your desired scenarios with critical inputs & outputs. Your problem should not be a very hard one and it should be easily solvable with plain php.So cheers :) – MD. Sahib Bin Mahboob Aug 29 '12 at 17:28

Adding time to mysql timestamp column, with condition

2 Answers2

Linked