CODEWITHSUNDEEP
phpmysqldateunixtimestamp
abhaystack
abhaystack

Reputation: 3

how to search between two mysql unix timestamp?

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

Upvotes: 0

Views: 1708

Answers (1)

Frankich
Frankich

Reputation: 852

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

Upvotes: 1

Related Questions