I am working on an API (PHP) which provides me time in Minutes:Seconds:Milli-seconds format (01:21:91). I don't know how to save it in mysql. I also need to make some operations in that time. Please help me with this.
Asked
Active
Viewed 246 times
0
Brian Tompsett - 汤莱恩
- 5,753
- 72
- 57
- 129
gautamlakum
- 11,815
- 23
- 67
- 90
-
Dont know if this will help. But seems relevant http://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision – Atul O Holic May 15 '14 at 06:16
-
I think you can pass datetime to mysql built in method like this it will return you miliseconds. MICROSECOND('2010-12-10 14:12:09.019473'); – Muhammad Saqlain Arif May 15 '14 at 06:17
-
1please tell us which mysql version you are using, there is a pretty easy way for mysql 5.6 and higher – CodeFanatic May 15 '14 at 06:19
-
I am using mysql version 5.5 – gautamlakum May 15 '14 at 06:22
-
@lakumg then u will have to split it into to rows – CodeFanatic May 15 '14 at 06:25
-
@FelixLahmer how? I am not getting idea here. :( – gautamlakum May 15 '14 at 06:29
-
@lakumg i gave you a working example, look my answer below, I will be away for like 30 mins now, if you need anything else, just comment EDIT: i meant columns before, not rows – CodeFanatic May 15 '14 at 06:35
1 Answers
2
Since you are not working with mySQL 5.6 or higher you have to split it into two columns. The one for the datetime (dttme) and the other one for the microsecond (mcrscnds)
CREATE TABLE microseconds (
dttme datetime,
mcrscnds int
);
INSERT INTO microseconds VALUES
('2014-05-15 09:22:37.456789', MICROSECOND('2014-05-15 09:22:37.456789'));
CodeFanatic
- 11,434
- 1
- 20
- 38