Posted by Matthew Bates on 06/27/05 02:51

Andy Hassall wrote:
> On Sat, 25 Jun 2005 23:06:23 GMT, Matthew Bates <mattybates@hotmail.com> wrote:
>
>
>>Andy Hassall wrote:
>>
>>>On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <mattybates@hotmail.com> wrote:
>>>
>>>
>>>>I'm currently storing start and end times (hh:mm:ss) for shows in a
>>>>radio schedule using MySQL and processing the data with PHP.
>>>>
>>>>I need to add one second to the end time to ensure it is formatted
>>>>correctly (e.g. 09:29:59 to 9:30am)
>>>
> [snip]
>
>>I thought that was the case however I'm not storing dates, I'm storing
>>times as the times can relate to any weekday/weekend day (e.g. 9:30:00
>>on a Saturday is programme x, 10:30:00 is programme y..).
>
>
> mysql> create table t (t time);
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into t values ('000000');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into t values ('000001');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into t values ('092959');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into t values ('235959');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select t, addtime(t, '000001') from t;
> +----------+----------------------+
> | t | addtime(t, '000001') |
> +----------+----------------------+
> | 00:00:00 | 00:00:01 |
> | 00:00:01 | 00:00:02 |
> | 09:29:59 | 09:30:00 |
> | 23:59:59 | 24:00:00 |
> +----------+----------------------+
> 4 rows in set (0.04 sec)
>
> Don't like the 24:00:00? Then how about:
>
> mysql> select sec_to_time(mod(time_to_sec(t)+1,86400)) from t;
> +------------------------------------------+
> | sec_to_time(mod(time_to_sec(t)+1,86400)) |
> +------------------------------------------+
> | 00:00:01 |
> | 00:00:02 |
> | 09:30:00 |
> | 00:00:00 |
> +------------------------------------------+
> 4 rows in set (0.03 sec)
>

Superb! The addtime function was exactly what I needed - thank you :).

Matt

[Back to original message]