Jump to content

Selecting records of the last 24 hours


PHP5000
Go to solution Solved by Barand,

Recommended Posts

Hi all

I have a mysql table called Ordertable, in it there is column where I save the date and time, by saving date("d.m.Y') I have made another column called OrderTimestamp which saves mktime($DD4), where $DD4 is date("H:i:s m.d.Y");

I have been trying to find a way to select the records created in the 24 hours before running the code without success.  I have tried at least a dozen suggestions I saw on different websites including stockexchange (which to my experience is a complete waste of time).  Apparently people who made those suggestions never actually tried them themselves.

This can't be a complicated task, why is it not working then?

Any idea how this can be done?

Thanks in advance

PS.  The reason for 2 columns is the many different ways that I tried and none worked.

Link to comment
Share on other sites

you need to use a DATETIME data type and store the values in a sql YYYY-MM-DD hh:mm:ss format (the equivalent php date() format specifier would be 'Y-m-d H:i:s') this will allow you to preform date comparisons directly on the values in the sql query statement. you would then use a WHERE clause in a query - WHERE your_datetime_column >= NOW() - INTERVAL 1 day to match records that are within one day of the current date.

edit:

Quote

mktime($DD4), where $DD4 is date("H:i:s m.d.Y")

supplying a a value that 'looks' like the list of parameters to a function call doesn't work. this is treating the whole value as the 1st parameter (hour) or is possibly producing a php error.

Edited by mac_gyver
Link to comment
Share on other sites

one primary reason has already been given -

5 hours ago, mac_gyver said:

this will allow you to preform date comparisons directly on the values in the sql query statement.

any format where the fields are not from left to right in most significant to least significant order cannot be directly compared by magnitude.

additional reasons for using a standard sgl data type for storing dates/datetimes are -

  1. it allows you to sort using the values (because date comparisons directly work on the values)
  2. it allows you to use all the built in sql date/time functions
  3. this standard format is also what php's date/datetime functions accept and use by default
  4. is uses the least amount of storage
  5. it results in the fastest queries

you store and operate on dates/datetimes internally using this standard format. you only format values in your local format when you display them.

if you have a lot of existing data stored in some other format, you can add a standard date or datetime data type column to your table, and use MySql's STR_TO_DATE(str,format) function in an UPDATE query to populate the new column from the existing values. once you have converted and tested your code to use the new column values, you can delete the old column from the table.

 

Edited by mac_gyver
Link to comment
Share on other sites

Gentlemen, I had a theory for a while that the world around me is getting dumber (at an accelerated rate since 5 years ago), just as everybody is getting older and I am staying wondering about it.  Now, I am beginning to give it a slight possibility that maybe I am not as smart as used to think I was, or I was. 

Please imagine that you are talking to an ignoramus, and do be kind enough to provide examples in code.  I learn by only example despite several degrees in computer science and engineering (:) yup, I am not kidding you).  Thanks in advance and let's all of us enjoy less dizziness on a flat earth.

Edited by PHP5000
Link to comment
Share on other sites

  • Solution

Random dates test data (TABLE: orders)

+----+---------------------+
| id | ordertimestamp      |
+----+---------------------+
|  1 | 06:00:00 10.30.2024 |
|  2 | 09:00:00 10.28.2024 |
|  3 | 01:00:00 10.31.2024 |
|  4 | 11:00:00 10.29.2024 |
|  5 | 14:00:00 10.29.2024 |
|  6 | 00:00:00 10.29.2024 |
|  7 | 00:00:00 10.30.2024 |
|  8 | 16:00:00 10.30.2024 |
|  9 | 12:00:00 10.28.2024 |
| 10 | 06:00:00 10.29.2024 |
| 11 | 18:00:00 10.29.2024 |
| 12 | 20:00:00 10.30.2024 |
| 13 | 11:00:00 10.30.2024 |
| 14 | 18:00:00 10.28.2024 |
| 15 | 10:00:00 10.30.2024 |
+----+---------------------+

Now add a proper datetime column.

ALTER TABLE orders ADD COLUMN newdatetime DATETIME;

Now transfer the old timestamp data to new column, reformatting the data

UPDATE orders SET newdatetime = STR_TO_DATE(ordertimestamp, '%H:%i:%s %m.%d.%Y');

Now we can list the data in date order

SELECT *
FROM orders
WHERE newdatetime > NOW() - INTERVAL 24 HOUR
ORDER BY newdatetime;

+----+---------------------+---------------------+
| id | ordertimestamp      | newdatetime         |
+----+---------------------+---------------------+
|  1 | 06:00:00 10.30.2024 | 2024-10-30 06:00:00 |
| 15 | 10:00:00 10.30.2024 | 2024-10-30 10:00:00 |
| 13 | 11:00:00 10.30.2024 | 2024-10-30 11:00:00 |
|  8 | 16:00:00 10.30.2024 | 2024-10-30 16:00:00 |
| 12 | 20:00:00 10.30.2024 | 2024-10-30 20:00:00 |
|  3 | 01:00:00 10.31.2024 | 2024-10-31 01:00:00 |
+----+---------------------+---------------------+

 

Link to comment
Share on other sites

10000 thanks Barand I tried it and worked like a charm.

As some of the replies suggested I set the format in mysql table as Y-m-d H:i:s .  It might have certain advantages, but it looks like putting the cart before the horse to me so I prefer to show the result as d-m-Y H:i.  however, when I try the code:

$DD=$row["OrderDate"];

$FormattedDate = date_format($DD,'d-m-Y H:i');

print $FormattedDate;

It shows nothing?

Thanks in advance

 

Edited by PHP5000
Link to comment
Share on other sites

two problems - 1) you don't have php's error_reporting set to E_ALL (it should always be this value) and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects, and 2) once you do that, you will get a php error about the 1st argument being a string instead of a datetime (Interface/immutable) object.

to use date_format() you must first create a datetime object from the fetched value - 

$DD = new datetime($row["OrderDate"]);

echo $FormattedDate = date_format($DD,'d-m-Y H:i');

 

Link to comment
Share on other sites

7 hours ago, PHP5000 said:

I prefer to show the result as d-m-Y H:i. 

You can do it in the query...

SELECT id
     , newdatetime AS stored_date
     , DATE_FORMAT(newdatetime, '%d/%m/%Y %H:%i') AS pretty_date
FROM orders
ORDER BY stored_date;

+----+---------------------+------------------+
| id | stored_date         | pretty_date      |
+----+---------------------+------------------+
|  2 | 2024-10-28 09:00:00 | 28/10/2024 09:00 |
|  9 | 2024-10-28 12:00:00 | 28/10/2024 12:00 |
| 14 | 2024-10-28 18:00:00 | 28/10/2024 18:00 |
|  6 | 2024-10-29 00:00:00 | 29/10/2024 00:00 |
| 10 | 2024-10-29 06:00:00 | 29/10/2024 06:00 |
|  4 | 2024-10-29 11:00:00 | 29/10/2024 11:00 |
|  5 | 2024-10-29 14:00:00 | 29/10/2024 14:00 |
| 11 | 2024-10-29 18:00:00 | 29/10/2024 18:00 |
|  7 | 2024-10-30 00:00:00 | 30/10/2024 00:00 |
|  1 | 2024-10-30 06:00:00 | 30/10/2024 06:00 |
| 15 | 2024-10-30 10:00:00 | 30/10/2024 10:00 |
| 13 | 2024-10-30 11:00:00 | 30/10/2024 11:00 |
|  8 | 2024-10-30 16:00:00 | 30/10/2024 16:00 |
| 12 | 2024-10-30 20:00:00 | 30/10/2024 20:00 |
|  3 | 2024-10-31 01:00:00 | 31/10/2024 01:00 |
+----+---------------------+------------------+

 

Link to comment
Share on other sites

20000 thanks to each of you.  I am not sure if my hosting company allows access to such settings mac_gyver this would be very helpful.  I am yet to find a good hosting company with proper tech support and services.  Barand it is not just for being pretty.  On what planet we start the date with year?  It is like starting an address with the country.  That said, you been very helpful and you can start the date with seconds as far as I am concerned.

Edited by PHP5000
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.