Posted by Andy Hassall on 06/26/05 02:26

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)

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Back to original message]