How to select last hour without timestamp in MySQL

Question

I would like to select all records from the last hour. Problem is, that I don't have an timestamp. I save the time in one row, and in another the date.

date        time
2015-11-27  10:55:04
2015-11-27  11:16:38
2015-11-27  11:57:29
2015-11-27  13:04:47
2015-11-27  14:29:00
2015-11-27  14:36:41
2015-11-27  14:52:46
2015-11-27  15:06:20
2015-11-27  15:30:24
2015-11-27  15:47:15

Is it possible to convert this to a timestamp in order to make the selection i wish according to this method: select rows from last 2 hours

thanks!

what have you tried sofar? And what are the datatypes of the date and time column? — deterministicFail, Dec 01 '15 at 10:54
1. (Obviously) Consider storing date and time as as single entity — Strawberry, Dec 01 '15 at 10:56
This convert datetime column like this select convert(datetime,'2015-11-27 '+' 10:55:04') — Mukesh Kalgude, Dec 01 '15 at 11:14

score 4 · Accepted Answer · edited Dec 01 '15 at 11:25

4

I think I could solve the problem by myself. Thanks for your hints.

SELECT *
FROM recs
WHERE TIMESTAMP(`datefield`,`timefield`) BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW();

edited Dec 01 '15 at 11:25

Gordon Linoff

1,242,037
58
646
786

answered Dec 01 '15 at 11:21

rockZ

815
1
12
28

score 1 · Answer 2 · answered Dec 02 '15 at 07:40

1

Just want to share other way around,

SELECT 
        * 
FROM
        <tablename>
WHERE 
        CAST(CONCAT(datecolumn,' ',timecolumn) AS DATETIME) > SUBDATE(NOW(), INTERVAL 1 HOUR);

answered Dec 02 '15 at 07:40

Hytool

1,358
1
7
22

How to select last hour without timestamp in MySQL

2 Answers2