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?

Date functions vary between DBMS. Please tag your DBMS so we can answer — JohnHC, May 16 '19 at 11:18
Yes.It can be completed,but which database are you using now? — Shawn.X, May 16 '19 at 11:18
can it be done as a part of query? because i am inserting in db in 2019-05-13 13:01:39 format and this cant be altered.I wanted the new format to be as a part of SQL query — Vysakh Gopalakrishnan, May 16 '19 at 11:24
Add 2019-05-03 00:31:31, 2019-05-03 11:31:31 and 2019-05-03 23:31:31 to your sample data, and adjust the expected result accordingly. — jarlh, May 16 '19 at 11:28

Voodoo · Answer 1 · 2019-05-16T11:36:58.290

2

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))

edited May 16 '19 at 11:36

answered May 16 '19 at 11:26

Voodoo

1,550
1
9
19

2

Here is the [real](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract) documentation – Raymond Nijland May 16 '19 at 11:29
1

Most likely the downvote was because you linked to w3schools..w3schools had very bad (outdated) information in the past it still might have they had the name w3fools or something like that. – Raymond Nijland May 16 '19 at 11:33
1

But + operator for string concatenation will not work in MySQL.. + operator is SQL Server (MSSQL) syntax by the way. MySQL uses `CONCAT()` for string concatenation – Raymond Nijland May 16 '19 at 11:35
Updated with proper `MySql` syntax – Voodoo May 16 '19 at 11:39

Joakim Danielson · Answer 2 · 2019-05-16T11:43:20.907

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

Convert time into range in SQL

2 Answers2