Convert time into range in SQL

Question

I have a datetime field which displays as eg: 2019-05-13 13:01:39.

I want this to come as 1-2pm or 13-14pm in SQL.

Likewise, 2019-05-03 19:31:31 should be displayed as 7-8 or 19-20.

Is this possible in SQL using query?

Answer 1

You can use the EXTRACT function of MySql to get any part of date or time. Visit Here for more details

SELECT CONCAT(EXTRACT(hour FROM yourdate) , '-' , (EXTRACT(hour FROM yourdate) + 1))

Answer 2

Here is a solution using DATE_FORMAT and DATE_ADD and a 24 hour clock

SELECT CONCAT(DATE_FORMAT('2019-05-03 19:31:31', '%H'), '-', 
              DATE_FORMAT(DATE_ADD('2019-05-03 19:31:31', INTERVAL 1 HOUR), '%H'))

and the same solution with a 12 hour clock

SELECT CONCAT(DATE_FORMAT('2019-05-03 19:31:31', '%h'), '-',
              DATE_FORMAT(DATE_ADD('2019-05-03 19:31:31', INTERVAL 1 HOUR), '%h %p'))

This will output

hour
19-20
07-08 PM

Interestingly this will give the following output if time is after 11 in the evening

hour
23-00
11-12 AM

Is this better than for instance 23-24 or is this just a bad way to display time? Maybe for the 12 hour clock it would be better to have AM/PM after each time

11 PM - 12 AM