2

I have a table with two columns that stores TIME type values. This is the CREATE statement:

CREATE TABLE `sdriving_turno` (
    `idturno` bigint(20) NOT NULL AUTO_INCREMENT,
    `idempresa` int(10) unsigned NOT NULL,
    `tipo` varchar(30) NOT NULL,
    `hora_inicio` time DEFAULT NULL,
    `hora_fin` time DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`idturno` , `idempresa`),
    KEY `fk_sdriving_turno_sdriving_empresa1_idx` (`idempresa`),
    CONSTRAINT `sdriving_turno_ibfk_1` FOREIGN KEY (`idempresa`)
        REFERENCES `sdriving_empresa` (`idempresa`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

I need to get the records in a range of TIME, lets say I need to get all the records BETWEEN 14:00 and 17:00 and for that I made this query (it has some JOINS but it's not relevant on this topic):

SELECT 
    s.idregistros AS s__idregistros,
    s.maquinaemisorid AS s__maquinaemisorid,
    s.idoperador AS s__idoperador,
    s.idturno AS s__idturno,
    s.created_at AS s__created_at,
    s.updated_at AS s__updated_at,
    s2.idturno AS s2__idturno,
    s2.idempresa AS s2__idempresa,
    s2.tipo AS s2__tipo,
    s2.hora_inicio AS s2__hora_inicio,
    s2.hora_fin AS s2__hora_fin,
    s2.created_at AS s2__created_at,
    s2.updated_at AS s2__updated_at,
    s3.idmaquinaemisor AS s3__idmaquinaemisor,
    s3.idmaquina AS s3__idmaquina,
    s3.idemisor AS s3__idemisor,
    s3.created_at AS s3__created_at,
    s3.updated_at AS s3__updated_at,
    s4.idmaquina AS s4__idmaquina,
    s4.idempresa AS s4__idempresa,
    s4.patente AS s4__patente,
    s4.created_at AS s4__created_at,
    s4.updated_at AS s4__updated_at,
    s5.idoperador AS s5__idoperador,
    s5.idempresa AS s5__idempresa,
    s5.nombre AS s5__nombre,
    s5.rut AS s5__rut,
    s5.created_at AS s5__created_at,
    s5.updated_at AS s5__updated_at,
    s6.iddetalle_emisores AS s6__iddetalle_emisores,
    s6.idregistros AS s6__idregistros,
    s6.fecha_registro AS s6__fecha_registro,
    s6.dertalle_mensaje AS s6__dertalle_mensaje,
    s6.estado AS s6__estado,
    s6.revisado AS s6__revisado,
    s6.modo AS s6__modo,
    s6.remitente AS s6__remitente,
    s6.created_at AS s6__created_at,
    s6.updated_at AS s6__updated_at
FROM
    sdriving_registros_emisores s
        LEFT JOIN
    sdriving_turno s2 ON s.idturno = s2.idturno
        LEFT JOIN
    sdriving_maquina_emisor s3 ON s.maquinaemisorid = s3.idmaquinaemisor
        LEFT JOIN
    sdriving_maquina s4 ON s3.idmaquina = s4.idmaquina
        LEFT JOIN
    sdriving_operador s5 ON s.idoperador = s5.idoperador
        LEFT JOIN
    sdriving_detalle_emisores s6 ON s.idregistros = s6.idregistros
WHERE
    (s5.idempresa = '12'
        AND (s2.hora_inicio < '14:47'
        AND '14:47' > s2.hora_fin))

But when record has hora_inicio = 00:00:00 and hora_fin = 17:00:00 I get no results, why? What's wrong in my query?

Note 14:47 is the server data build through PHP if there is any way to get the records according to the current time in server just using SQL way let me know.

3
  • I guess you wanted AND '14:47' < s2.hora_fin Jul 10, 2013 at 15:08
  • @ypercube aparently this fix the error, I'm doing some tests yet, will you answer a question in order to give your the points?
    – Reynier
    Jul 10, 2013 at 15:35
  • @ypercube I changed the values from hora_inicio = 00:00:00 and hora_fin = 17:00:00 to hora_inicio = 17:00:00 and hora_fin = 00:00:00` and query stop working, how I can fix that?
    – Reynier
    Jul 11, 2013 at 1:19

2 Answers 2

-1
SELECT * 
FROM `sdriving_turno` 
WHERE hora_inicio < NOW() AND hora_fin < '17:00:00'

you could also do something like

SELECT * 
FROM `sdriving_turno` 
WHERE hora_fin BETWEEN NOW() AND '18:00:00'
0
-3

Yes there is a way. You can use now() function in sql to get the current time of the server while fetching records like the folowing query example..

select emp_id,now() from employee_tbl ;

it will return all the records of emp_id column along with the current server time.

1
  • have you even tried to read the question .. i think answer has been given on title only
    – Dhaval
    Nov 25, 2013 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.