Best Way to Store Time in Format HH:MM in MySQL for Performance

Question

I am storing departure and arrival times as minutes(int) in database.

id, transport_date, departure_time, arrival_time
'3','2017-08-01',        '620',       '650'

and convert the minutes to time in JS: e.g:

public String getDepartureTime() {
    return minuteToTime(departureTime);
}



public static String minuteToTime(int minute) {
  int hour = minute / 60;
  minute %= 60;
  String p = "AM";
  if (hour >= 12) {
    hour %= 12;
    p = "PM";
  }
  if (hour == 0) {
    hour = 12;
  }
  return (hour < 10 ? "0" + hour : hour) + ":" + (minute < 10 ? "0" + minute : minute) + " " + p;
}

and this returns 620 to "10:20 AM". Is this a good approach to store time in database? or should I just change column datatype as varchar and save time as 10:20 AM? Which way is faster, or has better performance?

This link should help https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql — alistaircol, Jul 05 '17 at 10:03
In general, if I have a price I use `DECIMAL`, if I have a date I use `DATE` and if I have a time I use `TIME`. I need to have very specific use cases to stringify my data. Whatever, your overall design seems to assume that a transport can't last several days, make sure that underlying data complies with that. — Álvaro González, Jul 05 '17 at 10:15
@ÁlvaroGonzález How you define TIME datatype in java class? — Malena T, Jul 05 '17 at 12:21
I don't know much Java but I suppose it could have a constructor that accepts strings and then parses and stores the values in whatever protected properties it needs (e.g. `hour`, `minutes` or `seconds_from_midnight`). — Álvaro González, Jul 05 '17 at 12:32

score 1 · Accepted Answer · answered Jul 06 '17 at 06:58

Instead of storing times in INT type or Varchar, you should use "datetime" data type for those columns and while retrieving we can extract time from these column with functions.

Ex : Assuming that you have altered the datatypes to 'datetime' and your query will be

SELECT id, transport_date, Time(departure_time) As DepartureTime , Time(arrival_time) As ArrivalTime FROM Transport_TABLE;

Jark · Answer 2 · 2018-05-03T19:47:32.753

The way I do it, is storing a column of type DATETIME, but concatenating a fictitious date. E.g. 2018-01-01 13:05:00 (all years have a 1st January). Then when i want to get the time with a query I use the function DATE_FORMAT
E.g:

  `SELECT DATE_FORMAT(MyColDateTime, '%H:%i:%s') TIME`

Or also when I'm working with php, i use:

    `$date = new DateTime('MyColDateTime');`
    `$time = $date->format('H:i:s');`

The way of store in DATETIME , is useful because it gives you the possibility, to use all the functions to work both in Mysql and in your programming language.

Best Way to Store Time in Format HH:MM in MySQL for Performance

2 Answers2