Split and compare hour format in MySQL

Question

I'm continuing a project from someone else where they stored the time in hh:mm/hh:mm format in the MySQL database with the delimiter /

How do I split up and compare between those hours with the TIME datatype?

Answer 1

Considering there are a finite and known number of fields, you could use this (admittedly) ugly solution to split the fields using SUBSTRING_INDEX and cast them to TIME for further comparison/manipulation.

SELECT
    CAST(SUBSTRING_INDEX(monday,'/',1) AS TIME) AS 'monday_start',
    CAST(SUBSTRING_INDEX(monday,'/',-1) AS TIME) AS 'monday_end',
    CAST(SUBSTRING_INDEX(tuesday,'/',1) AS TIME) AS 'tuesday_start',
    CAST(SUBSTRING_INDEX(tuesday,'/',-1) AS TIME) AS 'tuesday_end',
    CAST(SUBSTRING_INDEX(wednesday,'/',1) AS TIME) AS 'wednesday_start',
    CAST(SUBSTRING_INDEX(wednesday,'/',-1) AS TIME) AS 'wednesday_end',
    CAST(SUBSTRING_INDEX(thursday,'/',1) AS TIME) AS 'thursday_start',
    CAST(SUBSTRING_INDEX(thursday,'/',-1) AS TIME) AS 'thursday_end',
    CAST(SUBSTRING_INDEX(friday,'/',1) AS TIME) AS 'friday_start',
    CAST(SUBSTRING_INDEX(friday,'/',-1) AS TIME) AS 'friday_end',
    CAST(SUBSTRING_INDEX(saturday,'/',1) AS TIME) AS 'saturday_start',
    CAST(SUBSTRING_INDEX(saturday,'/',-1) AS TIME) AS 'saturday_end',
    CAST(SUBSTRING_INDEX(sunday,'/',1) AS TIME) AS 'sunday_start',
    CAST(SUBSTRING_INDEX(sunday,'/',-1) AS TIME) AS 'sunday_end'
FROM times

^{DB Fiddle}

A tip of the cap to fellow Stack Overflow user madde74 for the inspiration in an older answer.

Answer 2

This will split and convert the thursday column's first time to data type TIME

SELECT TIME(SUBSTRING_INDEX(thursday,'/',1)) FROM YOUR_TABLE

Output

03:01:00

Answer 3

On the basis of your explanation, I assume that two time elements on either side of '/' are to and from time. In other words monday column could be divided into to columns monday_from and monday_to and 00:00/12:30 could then be split with monday_from having 00:00 and monday_to having 12:30.

Following steps will help you achieve what you are after

• You can split 00:00/12:30 using SUBSTRING_INDEX()

e.g. select SUBSTRING_INDEX('00:00/12:30', '/', 1) monday_to, SUBSTRING_INDEX('00:00/12:30', '/', -1) monday_from

• You can then append :00 to each to represent seconds using CONCAT(). This will be insignificant as that data is not being recorded
• Using str_to_date() function then you can convert this string into time

e.g. select str_to_date('12:30:00', %h:%i:%s) - You can then compare these as time

select 
str_to_date(CONCAT(SUBSTRING_INDEX('00:00/12:30', '/', 1), ':00'), %h:%i:%s) as monday_to, 
str_to_date(CONCAT(SUBSTRING_INDEX('00:00/12:30', '/', -1), ':00'), %h:%i:%s) as monday_from

Note: I don't have MySQL DB handy so, SQL is not checked for syntactic error, may have small but correctable errors.

Answer 4

i hope you can finish the rest...

create database t

   create table t (
   user_id int,
   dayofweek varchar(15),
   timerange varchar(15)
   )

   insert into t (user_id, dayofweek, timerange) select 83, 'monday', '00:00/12:30'
   <br>insert into t (user_id, dayofweek, timerange) select 83, 'tuesday', null
   <br>insert into t (user_id, dayofweek, timerange) select 83, 'wednesday', '00:00/24:00'
   <br>insert into t (user_id, dayofweek, timerange) select 83, 'thursday', '03:01/10:02'
   <br>insert into t (user_id, dayofweek, timerange) select 83, 'friday', '00:00/24:00'
   <br>insert into t (user_id, dayofweek, timerange) select 83, 'saturday', '00:00/24:00'
   <br>insert into t (user_id, dayofweek, timerange) select 83, 'sunday', '00:00/24:00'

select *, 
substring(timerange, 1, 2) as [timerange1hours], 
substring(timerange, 4, 2) as [timerange1minutes], 
substring(timerange, 7, 2) as [timerange2hours], 
substring(timerange, 10, 2) as [timerange2minutes],
convert(int, substring(timerange, 7, 2)) - convert(int, substring(timerange, 1, 2)) as [diffhours],

convert(int, substring(timerange, 10, 2)) - convert(int, substring(timerange, 4, 2)) as [diffminutes]
from t 
where timerange is not null


delete  from t