How to handle sport race time results formatting in SQL, preferrably MariaDB

mysql time format am/pm
mysql convert am/pm to 24 hours
convert time to 24 hour format in mysql
how to insert time with am/pm format into the database in php
mysql date format
php pdo example
how to insert time with am/pm in mysql
mysql time format php

Timetaking in many sports/competitions is typically accurate down to 1/100th of a second, some even down to 1/1000th of a second.

A couple of random examples from swimming, which uses 1/100th second accuracy (in the format 'minutes:seconds,100th of second'):

50m Backstroke 00:34,12

1500m Freestyle 18:49,02

The delimiter after the seconds field may be either comma or dot - both are seen in professional timetaking systems.

How is this formatting achieved in SQL / MariaDB and what field type should be used? I believe that it is necessary to use 1/1000 of a second accuracy when entering data but format the output to 1/100th in the query.

You should probably use MySQL/MariaDB TIME. See Fractional Seconds in Time Values

Your example above could be done like this (with result TIME(2)):

CREATE TABLE `swimming_race` (
    `swimmer_id` INT (10) unsigned NOT NULL
    ,`distance` SMALLINT (5) unsigned NOT NULL
    ,`style` VARCHAR(25) NOT NULL
    ,`result` TIME(2) NOT NULL
    );

INSERT INTO `swimming_race` (
    `swimmer_id`
    ,`distance`
    ,`style`
    ,`result`
    )
VALUES (
    '1'
    ,'50'
    ,'Backstroke'
    ,'0:00:34.12'
    )
    ,(
    '1'
    ,'1500'
    ,'Freestyle'
    ,'0:18:49.02'
    );

MySQL TIME_FORMAT() Function, How to handle sport race time results formatting in SQL, preferrably MariaDB. 58. April 02, 2019, at 09:50 AM. Timetaking in many sports/competitions is typically� this is correct database wise but is completely wrong humanwise. (it is a 100 base time number instead of a 60 base), So i need to divide this number by 1.6 in order to get my actual time. How, when i run an SQL command, can i get SQL to format the result of this one column so that it returns. value / 1.6 (instead of just value)

Thanks for suggestions. Formatting using datetime(2) is one option which will work, but the date part is not needed.

MariaDB [(none)]> SELECT CONVERT('2019-04-01 00:03:19.3200000',datetime(2));
+----------------------------------------------------+
| CONVERT('2019-04-01 00:03:19.3200000',datetime(2)) |
+----------------------------------------------------+
| 2019-04-01 00:03:19.32                             |
+----------------------------------------------------+
1 row in set, 1 warning (0.005 sec)

Using TIME(2) as my data type was spot on.

Out of historical interest only: When was this implemented in MariaDB / MySQL? I looked into a similiar issue 5-6 years ago but back then I was not able to find this :)

Thanks again :)

(The only proper) PDO tutorial, The TIME_FORMAT() function formats a time by a specified format. Syntax. TIME_FORMAT(time, format). Parameter Values. Parameter, Description. time� Now return the column to its previous format: SQL> COLUMN ENAME FORMAT A10 Copying Column Display Attributes. When you want to give more than one column the same display attributes, you can reduce the length of the commands you must enter by using the LIKE clause of the COLUMN command.

TIME(3) for storing.

REPLACE(col, ',', '.') before converting to TIME(3) so that commas will be turned into periods and not trip up the conversion.

mysql> SELECT CONVERT('0:1:23.456', TIME(3));
+--------------------------------+
| CONVERT('0:1:23.456', TIME(3)) |
+--------------------------------+
| 00:01:23.456                   |
+--------------------------------+

mysql> SELECT CONVERT('0:1:23,456', TIME(3));
+--------------------------------+
| CONVERT('0:1:23,456', TIME(3)) |
+--------------------------------+
| 00:01:23.000                   |   -- lost fraction because of comma
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '0:1:23,456' |
+---------+------+----------------------------------------------+

Completed examples:

mysql> SELECT CONVERT(REPLACE('0:1:23,456', ',', '.'), TIME(3));
+---------------------------------------------------+
| CONVERT(REPLACE('0:1:23,456', ',', '.'), TIME(3)) |
+---------------------------------------------------+
| 00:01:23.456                                      |
+---------------------------------------------------+

mysql> SELECT CONVERT(REPLACE('0:1:23,456', ',', '.'), TIME(3)) with_comma;
+--------------+
| with_comma   |
+--------------+
| 00:01:23.456 |
+--------------+

mysql> SELECT CONVERT(REPLACE('0:1:23.456', ',', '.'), TIME(3)) with_dot;
+--------------+
| with_dot     |
+--------------+
| 00:01:23.456 |
+--------------+

mysql> SELECT CONVERT(REPLACE('0:1:23.45', ',', '.'), TIME(3)) with_2_digits;
+---------------+
| with_2_digits |
+---------------+
| 00:01:23.450  |
+---------------+

SQL Injection Attacks by Example, fetchColumn(); Getting data out of statement in dozens different formats. SQL is a good example of a PDO wrapper with many additional features. How to multiple query results in order to reduce the query number? If you didn't care - it's all right too, as PHP is good with basic error handling and will conduct PDO� How to handle sport race time results formatting in SQL, preferrably MariaDB. READ ALSO. jQuery Slider. jShowOff: a jQuery Content Rotator. 259. jQuery Slider.

Come gestire lo sport risultati in tempo gara di formattazione in SQL , "SQL Injection" is subset of the an unverified/unsanitized user input vulnerability return only a single item each time, this version will essentially return every item We'd like to get some more names to work with, preferably those likely to the entire members table, which really doesn't seem too sporting. In SQL Server, you can use the T-SQL FORMAT () function to format the date and/or time. Simply provide two arguments; the date/time and the format to use. The format is supplied as a format string. A format string defines how the output should be formatted.

JSON with MariaDB Platform: What Is JSON and Why Use It – With , Probabilmente � meglio usare MySQL / MariaDB TIME . Vedere frazioni di secondo a valori di tempo. Il tuo esempio di cui sopra potrebbe essere fatto in questo� Also, take a look at FOR XML PATH and formatting query results into an HTML table. I have several scheduled jobs set up that use this method of extracting and displaying data.

Datetime behavior in MariaDB vs MySQL, Get to know JSON and learn why it's becoming the standard format for data This is an update of the popular post on JSON with MariaDB 10.2. treated as schemaless, whereas other data really should be handled more And then time moved on and people started buying things on the Happy SQL'ing I have been doing SQL for a while and I've always been satisfied to use the Results to Grid found in SSMS. Now I have a series of queries that I am running and I would like to have some very simple formatting of the results. Currently neither the Results to Grid nor the Results to Text do quite what I would like to do.

Comments
  • You can use the TIME datatype
  • This is the exact way I have set up the tables :) the TIME(2) was all I was missing.
  • Is there a manner in which to default format the time field without the hours part? I'll only need the hours in connection with Open Water (10 kilometer) events, which is a somewhat rare occurance.
  • @DavDav use TIME_FORMAT for the output. SELECT TIME_FORMAT(result, '%i:%s.%f') as short_res FROM swimming_race
  • Use TIME(3) to get 3 decimal places.