PHP5000 Posted October 30 Share Posted October 30 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 30 Share Posted October 30 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 1 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 30 Share Posted October 30 (edited) 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 Quote Link to comment Share on other sites More sharing options...
PHP5000 Posted October 30 Author Share Posted October 30 Thanks for the replies. What is the advantage of YYYY-mm-dd over dd-mm-YYYY ? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 30 Share Posted October 30 (edited) 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 - it allows you to sort using the values (because date comparisons directly work on the values) it allows you to use all the built in sql date/time functions this standard format is also what php's date/datetime functions accept and use by default is uses the least amount of storage 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 30 Share Posted October 30 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) Quote Link to comment Share on other sites More sharing options...
PHP5000 Posted October 30 Author Share Posted October 30 (edited) 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 October 30 by PHP5000 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted October 31 Solution Share Posted October 31 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 | +----+---------------------+---------------------+ Quote Link to comment Share on other sites More sharing options...
PHP5000 Posted October 31 Author Share Posted October 31 (edited) 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 October 31 by PHP5000 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 31 Share Posted October 31 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'); Quote Link to comment Share on other sites More sharing options...
Barand Posted October 31 Share Posted October 31 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 | +----+---------------------+------------------+ Quote Link to comment Share on other sites More sharing options...
PHP5000 Posted November 1 Author Share Posted November 1 (edited) 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 November 1 by PHP5000 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 1 Share Posted November 1 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. Quote Link to comment Share on other sites More sharing options...
gizmola Posted November 4 Share Posted November 4 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. Quote Link to comment Share on other sites More sharing options...
PHP5000 Posted Friday at 06:16 PM Author Share Posted Friday at 06:16 PM (edited) Good to see people are so cheerful and friendly. Gizmola thanks for the info. Generally speaking I code like an old craftsman building wooden toys. In this there is no right or wrong (and no sense is needed). Sometimes they work out fine and sometimes they need more tweaking, which makes the process more fun. Fortunately I only code for myself and I am a very good client Edited Friday at 06:22 PM by PHP5000 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.