[perleo]

Hi,

I need to use a mysql table type to allow times in the format

01:02:88 [hh:mm:ss]

If i use TIME it only allows up to 59 for the ss part.

Can somebody help me with this please.

[darkcarnival]

hi,

well i think it goes 59 then 0 like a clock does. but time should be the correct thing. if you need it in unix time i think theres a unix fuction but ill have to look

[YourPHPPro]

Originally Posted by perleo

01:02:88 [hh:mm:ss]

Why would you need the time to exceed 60 seconds ?

If for some reason you needed it to record greater than 60 seconds, what would the upper limit be ? 100 ? 1000 ?

[hyjacked]

Could you not make it a string field with an input mask that is NN:NN:NN?? where N has to be a number.

that is if mysql even supports input masks

[fyrestrtr]

I'm sorry to say, but this is a stupid way to enter information. You never need more than 60 seconds. If your script requires 80 to be in the seconds place, delete and rethink your script.

Use the TIMESTAMP column type.

Read more about it at the MySQL Date and Time manual section.

[perleo]

Originally Posted by fyrestrtr

I'm sorry to say, but this is a stupid way to enter information. You never need more than 60 seconds. If your script requires 80 to be in the seconds place, delete and rethink your script.

Use the TIMESTAMP column type.

Read more about it at the MySQL Date and Time manual section.

No its not.

The reason i need this type of time is for an motorsport event. There are not really seconds, but miliseconds with go upto 99ms.

Back to YourPHPPro post, it will have to go upto 99

Can you tell me if it can be done ?

[YourPHPPro]

Originally Posted by perleo

No its not. The reason i need this type of time is for an motorsport event.

Wouldn't you need something like this then :

hh:mm:ss.nnn

where h = hours, m = minutes, s = seconds, and nnn = milliseconds

I'd go with storing it as a char(12) in mySQL which is basically what hyjacked said.

[perleo]

Originally Posted by YourPHPPro

Wouldn't you need something like this then :

hh:mm:ss.nnn

where h = hours, m = minutes, s = seconds, and nnn = milliseconds

I'd go with storing it as a char(12) in mySQL which is basically what hyjacked said.

then I wont be able to sort them. There will be hundreds of these and I cant look at them individually and sort them

[YourPHPPro]

Originally Posted by perleo

then I wont be able to sort them. There will be hundreds of these and I cant look at them individually and sort them

Code:

DROP TABLE IF EXISTS `test_sec`;
CREATE TABLE `test_sec` (
  `test_id` int(3) NOT NULL auto_increment,
  `test_time` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`test_id`),
  KEY `test_time` (`test_time`)
) TYPE=MyISAM AUTO_INCREMENT=25 ;

#
# Dumping data for table `test_sec`
#

INSERT INTO `test_sec` VALUES (1, '01:01:01.120');
INSERT INTO `test_sec` VALUES (2, '01:01:01.121');
INSERT INTO `test_sec` VALUES (3, '01:01:01.122');
INSERT INTO `test_sec` VALUES (4, '01:01:01.123');
INSERT INTO `test_sec` VALUES (5, '01:01:01.124');
INSERT INTO `test_sec` VALUES (6, '01:01:01.125');
INSERT INTO `test_sec` VALUES (7, '01:01:02.120');
INSERT INTO `test_sec` VALUES (8, '01:01:02.121');
INSERT INTO `test_sec` VALUES (9, '01:01:02.122');
INSERT INTO `test_sec` VALUES (10, '01:01:02.123');
INSERT INTO `test_sec` VALUES (11, '01:01:02.124');
INSERT INTO `test_sec` VALUES (12, '01:01:02.125');
INSERT INTO `test_sec` VALUES (13, '01:02:01.120');
INSERT INTO `test_sec` VALUES (14, '01:02:01.121');
INSERT INTO `test_sec` VALUES (15, '01:02:01.122');
INSERT INTO `test_sec` VALUES (16, '01:02:01.123');
INSERT INTO `test_sec` VALUES (17, '01:02:01.124');
INSERT INTO `test_sec` VALUES (18, '01:02:01.125');
INSERT INTO `test_sec` VALUES (19, '02:01:01.120');
INSERT INTO `test_sec` VALUES (20, '02:01:01.121');
INSERT INTO `test_sec` VALUES (21, '02:01:01.122');
INSERT INTO `test_sec` VALUES (22, '02:01:01.123');
INSERT INTO `test_sec` VALUES (23, '02:01:01.124');
INSERT INTO `test_sec` VALUES (24, '02:01:01.125');

The sql command:

Code:

SELECT * 
FROM `test_sec` 
ORDER BY test_time DESC

yields

Code:

Generated by: phpMyAdmin 2.5.4
SQL-query: SELECT * FROM `test_sec` ORDER BY test_time DESC LIMIT 0, 30; 
test_id 	test_time 	
24	02:01:01.125	
23	02:01:01.124	
22	02:01:01.123	
21	02:01:01.122	
20	02:01:01.121	
19	02:01:01.120	
18	01:02:01.125	
17	01:02:01.124	
16	01:02:01.123	
15	01:02:01.122	
14	01:02:01.121	
13	01:02:01.120	
12	01:01:02.125	
11	01:01:02.124	
10	01:01:02.123	
9	01:01:02.122	
8	01:01:02.121	
7	01:01:02.120	
6	01:01:01.125	
5	01:01:01.124	
4	01:01:01.123	
3	01:01:01.122	
2	01:01:01.121	
1	01:01:01.120

Is that what you want ?

[perleo]

not really,

it cant go by and auto incremement. The times will be entered by anyone, but they will have to be sorted with the shortest time. No-body will know which time is the quickest until they are printed out in order.

So can mysql sort times of that format?