Times

[Matthew Bates]

Hi,

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) however I just cannot see how to do
it *correctly* - with PHP or MySQL. I may well be overlooking something
but I would be grateful if anybody could help and point me in the right
direction.

Thanks in advance.

Cheers,
Matt

[Geoff Berrow]

Re: Times

I noticed that Message-ID:
<uAkve.56488$G8 .5111@text.news .blueyonder.co. uk> from Matthew Bates
contained the following:
[color=blue]
>I need to add one second to the end time to ensure it is formatted
>correctly (e.g. 09:29:59 to 9:30am) however I just cannot see how to do
>it *correctly* - with PHP or MySQL. I may well be overlooking something
>but I would be grateful if anybody could help and point me in the right
>direction.[/color]


If you are storing this as a date, get a UNIX timestamp, add the second,
and format using PHP.

$sql="SELECT *, UNIX_TIMESTAMP( $datefield) AS unixdate FROM `table`
";
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Matthew Bates]

Re: Times

Geoff Berrow wrote:[color=blue]
> I noticed that Message-ID:
> <uAkve.56488$G8 .5111@text.news .blueyonder.co. uk> from Matthew Bates
> contained the following:
>
>[color=green]
>>I need to add one second to the end time to ensure it is formatted
>>correctly (e.g. 09:29:59 to 9:30am) however I just cannot see how to do
>>it *correctly* - with PHP or MySQL. I may well be overlooking something
>>but I would be grateful if anybody could help and point me in the right
>>direction.[/color]
>
>
>
> If you are storing this as a date, get a UNIX timestamp, add the second,
> and format using PHP.
>
> $sql="SELECT *, UNIX_TIMESTAMP( $datefield) AS unixdate FROM `table`
> ";[/color]

Thanks for your quick reply.

I'm storing them as times, as they relate to any weekday/weekend day
rather than a specific date.

Matt

[Geoff Berrow]

Re: Times

I noticed that Message-ID:
<6Jkve.56503$G8 .22523@text.new s.blueyonder.co .uk> from Matthew Bates
contained the following:
[color=blue][color=green]
>>
>> $sql="SELECT *, UNIX_TIMESTAMP( $datefield) AS unixdate FROM `table`
>> ";[/color]
>
>Thanks for your quick reply.
>
>I'm storing them as times, as they relate to any weekday/weekend day
>rather than a specific date.[/color]

I've not tried it, but it will probably work.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Andy Hassall]

Re: Times

On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <mattybates@hot mail.com> wrote:
[color=blue]
>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) however I just cannot see how to do
>it *correctly* - with PHP or MySQL. I may well be overlooking something
>but I would be grateful if anybody could help and point me in the right
>direction.[/color]

mysql> select d, d + INTERVAL 1 SECOND from t;
+---------------------+-----------------------+
| d | d + INTERVAL 1 SECOND |
+---------------------+-----------------------+
| 2005-06-25 09:29:59 | 2005-06-25 09:30:00 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

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

[Matthew Bates]

Re: Times

Andy Hassall wrote:[color=blue]
> On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <mattybates@hot mail.com> wrote:
>
>[color=green]
>>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) however I just cannot see how to do
>>it *correctly* - with PHP or MySQL. I may well be overlooking something
>>but I would be grateful if anybody could help and point me in the right
>>direction.[/color]
>
>
> mysql> select d, d + INTERVAL 1 SECOND from t;
> +---------------------+-----------------------+
> | d | d + INTERVAL 1 SECOND |
> +---------------------+-----------------------+
> | 2005-06-25 09:29:59 | 2005-06-25 09:30:00 |
> +---------------------+-----------------------+
> 1 row in set (0.00 sec)
>[/color]

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..).

[Andy Hassall]

Re: Times

On Sat, 25 Jun 2005 23:06:23 GMT, Matthew Bates <mattybates@hot mail.com> wrote:
[color=blue]
>Andy Hassall wrote:[color=green]
>> On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <mattybates@hot mail.com> wrote:
>>[color=darkred]
>>>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)[/color]
>>[/color][/color]
[snip][color=blue]
>
>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..).[/color]

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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Matthew Bates]

Re: Times

Andy Hassall wrote:[color=blue]
> On Sat, 25 Jun 2005 23:06:23 GMT, Matthew Bates <mattybates@hot mail.com> wrote:
>
>[color=green]
>>Andy Hassall wrote:
>>[color=darkred]
>>>On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <mattybates@hot mail.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)
>>>[/color][/color]
> [snip]
>[color=green]
>>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..).[/color]
>
>
> 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)
>[/color]

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

Matt