CODEWITHSUNDEEP
mysqltime
Malena T
Malena T

Reputation: 371

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

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?

Upvotes: 1

Views: 7280

Answers (2)

Jark
Jark

Reputation: 19

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.

Upvotes: 1

Jethan
Jethan

Reputation: 71

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;

Upvotes: 1

Related Questions