12 means 00 in hour

Databases
1 
America 20-07-19 am 12:47
Korea   20-07-19 am 03:04
Japan   20-07-19 am 10:20
Canada  20-07-19 pm 12:03
Germany 20-07-19 pm 05:35
France  20-07-19 pm 10:44
China   20-07-19 pm 11:20

I have a table like the above.

code1

SELECT country, dateTime FROM test ORDER BY country

The code1 above produces the result1 below.

result1

America 20-07-19 am 12:47
Canada  20-07-19 pm 12:03
China   20-07-19 pm 11:20
France  20-07-19 pm 10:44
Germany 20-07-19 pm 05:35
Japan   20-07-19 am 10:20
Korea   20-07-19 am 03:04
code2

SELECT country, dateTime FROM test ORDER BY dateTime

And the code2 above produces the result2 below.

result2

Korea   20-07-19 am 03:04
Japan   20-07-19 am 10:20
America 20-07-19 am 12:47
Germany 20-07-19 pm 05:35
France  20-07-19 pm 10:44
China   20-07-19 pm 11:20
Canada  20-07-19 pm 12:03

Since 12 means 00 in hour, I like to make the target result below with "ORDER BY dateTime.

target result

America 20-07-19 am 12:47
Korea   20-07-19 am 03:04
Japan   20-07-19 am 10:20
Canada  20-07-19 pm 12:03
Germany 20-07-19 pm 05:35
France  20-07-19 pm 10:44
China   20-07-19 pm 11:20
2

Why are you using those strings for datetime, rather than than standard datetime format?

3

Because It outputs the very string itself directly on the web page, but it produces a problem like the above on the search page.

Do you mean I should change the datetime to unixtime or standard datetime?

4

yes, change the column to DATETIME

then display it however you want with the DATE_FORMAT() function, while sorting on the DATETIME column (not the formatted date)

1 Like
5

I always use a TIMESTAMP, should I change to DATETIME?

6

lots of articles out there in the internet explaining the difference, and the pros and cons of either… i wouldn’t want to presume to know your needs

2 Likes
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.