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]

I noticed that Message-ID:
<uA*****************@text.news.blueyonder.co.uk> from Matthew Bates
contained the following:

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.

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]

Geoff Berrow wrote:

I noticed that Message-ID:
<uA*****************@text.news.blueyonder.co.uk> from Matthew Bates
contained the following:

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.

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`
";

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]

I noticed that Message-ID:
<6J******************@text.news.blueyonder.co.uk > from Matthew Bates
contained the following:

$sql="SELECT *, UNIX_TIMESTAMP($datefield) AS unixdate FROM `table`
";

Thanks for your quick reply.

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

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]

On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <ma********@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) 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.

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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Matthew Bates]

Andy Hassall wrote:

On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <ma********@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) 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.

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)

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]

On Sat, 25 Jun 2005 23:06:23 GMT, Matthew Bates <ma********@hotmail.com> wrote:

Andy Hassall wrote:

On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <ma********@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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Matthew Bates]

Andy Hassall wrote:

On Sat, 25 Jun 2005 23:06:23 GMT, Matthew Bates <ma********@hotmail.com> wrote:

Andy Hassall wrote:

On Sat, 25 Jun 2005 22:07:22 GMT, Matthew Bates <ma********@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