datetime field problem

[charlie321]

I am trying to import into a csv file and filter the results to a particular user and only results based on the time the data was uploaded within one hour.  In other words anything not uploaded within the last hour of the start_time will not appear in the csv file.  Everything works fine except for the time part.  I get no results at all with the 'and unix_timestamp........   For all I know possibly I am way off with my effort.  The field name is start_time.  Here is code below.  Appreciate any help.

<code>  header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
$then = time() + 3601;
$query = "SELECT * FROM ppb_listings where user_id = '4' and unix_timestamp(start_date) < $then";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_row($result)) {
    fputcsv($fp, $row);
}
echo $result;
exit;

</code>

[Barand]

How is your date stored in ppb_listings?

Just to be sure, if it is 7pm now, you want all records with a start time before 8pm today, correct?

[charlie321]

Hi I am sorry.  I forgot to mention that.  start_time is a datetime field.  And yes..  If starttime is 7pm, and query for the csv file, all records starting at 7pm would show up show up on the csv file until approximately 7:59 PM.   

Thanks for your consideration.  

[Barand]

I can't see a specific problem with your code, but it's not how I would write it

$ono = 123;
$filename = 'mytest.csv';

$res = $pdo->prepare("SELECT * 
                     FROM production_data
                     WHERE insert_time < NOW() + INTERVAL 60 MINUTE
                     AND order_id = ?
                     ");
$res->execute([$ono]);

header('Content-type: application/csv');
header("Content-Disposition: attachment; filename=$filename");

$fp = fopen('php://output', 'w');
foreach ($res as $row) {
    fputcsv($fp, $row);
}
fclose($fp);

 

[charlie321]

Thanks for the info.  I tried your query and does the same thing.  (nothing)   I'm wondering if possibly it has something to do with sql or something.  I even tried making it a week and it still finds nothing.

Anyone have any other suggestions?

[Barand]

Can you attach a dump of your data?

[charlie321]

Hi...  Thanks so much for your help.  Think I figured it out.  Works this way:

<code> $then = time() - 3601;
$query = "SELECT * FROM ppb_listings where user_id = '4' and unix_timestamp(start_time) > $then";

</code>

Nothing better than finally figuring out a stupid problem!!

 

[Barand]

Just think how much quicker we coud have got you there if you'd said what you were really trying to!

On 7/17/2021 at 7:03 PM, Barand said:

Just to be sure, if it is 7pm now, you want all records with a start time before 8pm today, correct?

The answer to that question should have been "No, I want all those with a time after 6pm today"