CODEWITHSUNDEEP
mysqlsqldatedatetime
Rohit
Rohit

Reputation: 370

Get records between Two Dates with overlapping time intervals

I have the following database

CREATE TABLE `table` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`time` bigint(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`messages` varchar(2000) NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `table` VALUES (1,1467311473,"Jim", "Jim wants a book"),
                      (2,1467226792,"Tyler", "Tyler wants a book"),
                      (3,1467336672,"Phil", "Phil wants a book");

I need to get the records between date 29 Jun 2016 and 1 July 2016 for time intervals 18:59:52 to 01:31:12. I wrote a query but it doesn't return the desired output

SELECT l.*
FROM table l
WHERE ((time >=1467226792) AND (CAST(FROM_UNIXTIME(time/1000) as time) >= '18:59:52') AND (CAST(FROM_UNIXTIME(time/1000) as time) <= '01:31:12') AND (time <=1467336672))

Any suggestions??

Upvotes: 0

Views: 139

Answers (2)

Strawberry
Strawberry

Reputation: 33935

As I understand it, you're simply interested in all periods greater than '2016-06-29 18:59:52' and less than '2016-07-01 01:31:12' where the time element is NOT between '01:31:12' and '18:59:52'

I think you can turn that logic into sql without further assistance

Ah, well, here's a fiddle - left out all the from_unixtime() stuff because it adds unnecessary complication to an understanding of the problem - but adapting this solution to your needs is literally just a case of preceding each instance of the column time with that function:

http://rextester.com/OOGWB23993

Upvotes: 2

Serg
Serg

Reputation: 22811

If i got it right

SELECT l.*
FROM `table` l
WHERE  time >=1467226792
       AND time <=1467336672
       AND CAST(FROM_UNIXTIME(time/1000) as time) >= '18:59:52' 
       AND FROM_UNIXTIME(time/1000) <= DATE_ADD(DATE_ADD(DATE_ADD(CAST(FROM_UNIXTIME(time/1000) as date), INTERVAL 25 HOUR), INTERVAL 31 MINUTE), INTERVAL 12 SECOND)

Upvotes: 0

Related Questions