how to search between two mysql unix timestamp?

Question

Hi mysql unix timestamp are stored in date column of my table now i am searching from date picker with two fields i.e. from_date and to_date but I did not found any records tried a lot of ways but not succeeded my date picker date is like this 08/22/2017 while unix timestamps stored in table are like

1503380449
1503380428
1503380326

how can i fire mysql query to get records between two dates I tried mysql UNIX timestamp functions but not working.

I tried this :-

SELECT * FROM tbl_example WHERE
my_date BETWEEN 
STR_TO_DATE('2017/08/22', '%Y %D %M %h:%i:%s %x')
AND
STR_TO_DATE('2017/08/22', '%Y %D %M %h:%i:%s %x')

I tried this:-

SELECT * FROM tbl_example WHERE
my_date BETWEEN 
UNIX_TIMESTAMP(STR_TO_DATE('2017/08/22', '%M %d %Y %h:%i%p')
AND
UNIX_TIMESTAMP(STR_TO_DATE('2017/08/22', '%M %d %Y %h:%i%p')

I also tried this:-

SELECT * FROM tbl_example WHERE
my_date BETWEEN 
FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Aug 22 2017 12:00AM', '%M %d %Y %h:%i%p')))
AND
FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Aug 22 2017 12:00AM', '%M %d %Y %h:%i%p')))

thanks

you have to parse the value of datepicker to change it in timestamp to compare, you should have a look on php.net/manual/en/function.strtotime.php — Frankich
– Frankich, Commented Aug 25, 2017 at 6:51
@MacBooc I want all record of particular date using that timestamp stored in mysql table — abhaystack
– abhaystack, Commented Aug 25, 2017 at 6:56
@MacBooc I am using zend framework i wrote this code $select->where("my_date BETWEEN UNIX_TIMESTAMP('".$from_date."00:00:00') AND UNIX_TIMESTAMP('".$to_date."00:00:00')"); but its not working while if i fire its query then its working but i am not getting same date result means if from date and to date are same then i am not getting records — abhaystack
– abhaystack, Commented Aug 25, 2017 at 8:43
Then i'll edit my code because the problem here is that i put two date at the begining of the day, tell me if it's ok to you — Frankich
– Frankich, Commented Aug 25, 2017 at 9:25

Frankich · Accepted Answer · 2017-08-25 11:49:26Z

1

if you want to do in mysql you should use the UNIX_TIMESTAMP function

PHP:

$from_date = "08/22/2017";
$from_date = substr($from_date, 6, 4) . "-" . substr($from_date, 0, 2) . "-" . substr($from_date, 3, 2);
$to_date = "08/30/2017";
$to_date = substr($to_date, 6, 4) . "-" . substr($to_date, 0, 2) . "-" . substr($to_date, 3, 2);

MYSQL :

"SELECT * FROM tbl_example WHERE
my_date BETWEEN 
UNIX_TIMESTAMP('".$from_date." 00:00:00')
AND
UNIX_TIMESTAMP('".$to_date." 23:59:59')" // changed here to have the end of the day in case that the query is on the same day

or PHP :

$from_date = strtotime("08/22/2017");
$to_date = strtotime("08/30/2017");

MYSQL :

"SELECT * FROM tbl_example WHERE
my_date BETWEEN 
".$from_date."
AND
".$to_date

edited Aug 25, 2017 at 11:49

answered Aug 25, 2017 at 7:09

Frankich

8529 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abhaystack Over a year ago

but i wanna suggest you that rather then doing substr for conversion we can simply use this way date('Y-m-d',strtotime("08/22/2017")); what you say ?

Frankich Over a year ago

Yeah it's also a good idea, BUT if you want to use this, you can directly use strtotime("08/22/2017") to have the timestamp, i thought that you wanted to do in SQL i'll edit to show you

Collectives™ on Stack Overflow

how to search between two mysql unix timestamp?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related