Get records based on date column value and time range arguments

Asked Jul 18 '18 at 02:10

Active Jul 18 '18 at 07:32

Viewed 61 times

I'd like to return records in a MySQL database based on whether the record date falls in between a time range (using PDO).

Time Range:

$rangeStart = "17-Jul-18 04:45 pm";
$rangeEnd = "17-Jul-18 05:30 pm";

Method:

public function getForTokenIDAddressAndDateRange($token_id, $address, $rangeStart, $rangeEnd) {

    $sql = "SELECT * FROM `{$this->table}` WHERE `token_id` = ? AND `address` = ? AND `date` <= ? AND `date` >= ?";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([$token_id, $address, strtotime($rangeStart), strtotime($rangeEnd)]);

}

Example "date" value from a record:

17-Jul-18 5:04 pm

How can I make the above method work properly? I believe I need to convert the "date" column value to a timestamp, but not sure how. thx!

edited Jul 18 '18 at 02:16

asked Jul 18 '18 at 02:10

cpcdev

1,130
3
18
45

does your date field in timestamp? – david Jul 18 '18 at 02:13
no its a varchar currently – cpcdev Jul 18 '18 at 02:14
I just fixed it, sorry that was an error only here on SO – cpcdev Jul 18 '18 at 02:15
there is something wrong in your `execute`. `[]` shouldn't have used. – david Jul 18 '18 at 02:15
Thanks, I fixed that as well. How about converting the date column value to a timestamp? I think thats the last step now. thank you again – cpcdev Jul 18 '18 at 02:18
It's quite difficult. Check this link. https://community.teradata.com/t5/UDA/convert-varchar-to-timestamp/td-p/32302 – david Jul 18 '18 at 02:19
What happens when you run that query directly with those values? Get that working first without php. Covert field to datetime if needed. Going to look at this tomorrow as I am curious how to solve. – Bleach Jul 18 '18 at 03:52
https://stackoverflow.com/questions/5786649/mysql-get-record-count-between-two-date-time – Bleach Jul 18 '18 at 03:54
I think the values you pass in will have to be military time instead of am/pm. And your mysql column needs to be datetime. – Bleach Jul 18 '18 at 03:56
`date` <= ? AND `date` >= ? weird condition. – munsifali Jul 18 '18 at 04:35
Always store data using the correct data type - otherwise there's not much point using SQL at all. – Strawberry Jul 18 '18 at 07:13

Get records based on date column value and time range arguments

0 Answers0