Selecting records of the last 24 hours

[PHP5000]

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.

[Barand]

When storing  dates in database always use yyyy-mm-dd format, and store in a DATE type  column (or date time type if you need the time too.

 

See https://forums.phpfreaks.com/topic/325220-date-help/?do=findComment&comment=1638885

 

[mac_gyver]

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 October 30 by mac_gyver

[PHP5000]

Thanks for the replies.  What is the advantage of YYYY-mm-dd over dd-mm-YYYY ?

[mac_gyver]

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 October 30 by mac_gyver

[Barand]

YYYY-MM-DD dates are far more efficient (store data for functionality, not prettiness). They ...

• can be sorted
• can be compared (earlier/later than)
• can be used directly by the dozens of MySql date/time functions without prior conversion

(Beaten to the post)

[PHP5000]

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 Wednesday at 10:12 PM by PHP5000

[Barand]

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 |
+----+---------------------+---------------------+

 

[PHP5000]

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 Thursday at 12:44 PM by PHP5000

[mac_gyver]

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');

 

[Barand]

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 |
+----+---------------------+------------------+

 

[PHP5000]

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 Friday at 01:38 AM by PHP5000

[Barand]

19 hours ago, PHP5000 said:

On what planet we start the date with year? 

This one - in millions of database tables the world over.

But if you don't have the sense to follow the best-practice approach then be prepared for slower queries, more date problems and more work.

[gizmola]

On 10/31/2024 at 6:34 PM, PHP5000 said:

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.

 

You are free to display the date however you choose.  I want to go one step further with this, and explain that a date/datetime is relative to the locale setting for the server.  

There really is only one right option in my opinion and that is for the server to utilize UTC/GMT, so when a value gets stored it is assumed to be UTC.  Most likely your hosting company has configured the mysql server to use UTC as its locale/timezone but you should probably check.

What should you display to an end user?  What they should see is relative to THEIR locale.  

Now you may very well want to hardcode this into your application if all your users are in one place (let's say they are in New York), which means that when you get date value from the database, you then need to convert it to the date/date time for the locale you want to present it to.

The PHP datetime class has methods that do all of this for you nicely, and there are also some great PHP libraries out there that will do all sorts of fancy date to "some string" manipulations for you, depending on what you want.

One I'm pretty familiar with, and is widely used is Carbon.

At minimum you want to take a look at the PHP Datetime class and look at how to create and use datetime objects.