Clash Royale CLAN TAG#URR8PPP

MySQL MAX and MIN on Varchar

I have a table that contains 3 columns; day_id, start_date, end_date. start_date and end_date are varchar(8) in a format like this HH:II:SS. Sometimes dates can go over 24h in order to represent that something happened day after, for example: 25:20:01 is 01:20:01 but in a new day. day_id is not unique, it repeats. I need to get first and last event of a day, and this is my code:

day_id

start_date

end_date

start_date

end_date

HH:II:SS

day_id

SELECT day_id,
MIN(start_date) as start_time,
MAX(end_date) as end_date
FROM events WHERE day_id IN ('day_1', 'day_2', 'day_3')
GROUP BY day_id ORDER BY start_time ASC


It works as intended but I can't figure out why, how does MySQL know that 25:01:45 is larger than 20:21:09 since they are both varchars? The whole table is in utf8mb4_0900_ai_ci collation, running on MySQL server version 8.

25:01:45

20:21:09

utf8mb4_0900_ai_ci

'2'='2'

'5'>'0'

'25:01:45' > '20:21:09'

2 Answers
2

It is a string comparison and it compares characters with their ascii value as you know. But it mainly works because it represents both single digits and two digits of time parameters as two digit representation. For example-

1:20:1

01:20:01

2:5:7

02:05:07

So, there will never be a time where 10:02:07 will come before 2:5:7(since 1 < 2) since 2:5:7 is 02:05:07 and 1 > 0. Hence, it always works.

10:02:07

2:5:7

1

2

2:5:7

02:05:07

1

0

Sometimes dates can go over 24h in order to represent that something
happened day after, for example: 25:20:01 is 01:20:01

So, if this 25 goes over 2 digits for some reason, then you will have problems. So, use the correct datatype to store it - TIME.

25

TIME

how does MySQL know that 25:01:45 is larger than 20:21:09?

Databases compare strings using a collation. The default collation is alphabetical ordering.

So, MySQL knows that '25' > '20' in exactly the same way that we knows that the word 'BE' comes after 'BA' in the dictionary.

'25'

'20'

'BE'

'BA'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.