Question 246324 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql TIME confusion

Created a table with some columns of type TIME(3) Loaded from a CSV file

0:07.763
0:07.783
0:07.276
0:07.484
0:07.127

Running select * from some_table yields the following:

00:00:07.763
00:00:07.783
00:00:07.276
00:00:07.484
00:00:07.127

My question is: How can I get this to display in a select statement without the leading 0’s ? Something like:

7.763
7.783
7.276
7.484
7.127

tried various permutations of TRIM but those all failed.

Tags:

2

Answers


  1. 0

    You can use the TIME_FORMAT function to format your times in your query.

    Something like this should work:

    select ROUND(TIME_FORMAT(time1, '%s.%f'), 3) from some_table

    The %s part is the seconds and the %f part is the microseconds.

    Login or Signup to reply.
  2. 0

    Exactly for this purpose the function TIME_TO_SEC() exists:

    SELECT TIME_TO_SEC(time1) FROM some_table

    This is much faster, since the function does a simple mathematical operation (tm.hour * 3600 + tm.minute * 60 + tm.second + tm.microsecond / 1000000) instead of parsing a string before:

    select benchmark(10000000, time_to_sec("00:00:07.363"));
0.097 sec

select benchmark(10000000, round(time_format("00:00:07.363", "%s.%f"),3))
2.215 sec

    But many roads lead to Rome:…

    SELECT time1 + 0 FROM some_table

SELECT CAST(time1 AS DECIMAL(4,3)) FROM some_table

SELECT SECOND(time1) + MICROSECONDS(time1) / 1000000 FROM some_table

SELECT ROUND(TIME_FORMAT(time1, '%s.%f'), 3) FROM some_table

SELECT ROUND(RIGHT(time1,6),3) FROM some_table
...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search