Hi,
I am trying to make a invoice system.
Im trying to make a cronjob to automatically run a php script but I dont know the right SQL query.
if expirydate in table is less than 30 days run script…
I tried this:
SELECT id FROM data_myservices WHERE DATEDIFF(day, Now(), expires) < 30
First Im trying to check how many. then I am going to make a script for each ID.
Assuming you posted this in the desired forum section, that’s not the MySql datediff() syntax. You would have been getting an sql error. MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions
Or just compare it like this:
mysql> SELECT id FROM data_myservices WHERE expires > now() - INTERVAL 30 day;
Thanks for the answer, I have been checking it now. I changed it to 60 days.
SELECT id FROM data_myservices WHERE expires > now() - INTERVAL 60 day
I get empty - 0 rows but a lot of warnings:
Warning: #1292 Truncated incorrect datetime value: ‘4073497200’
I gues it because my dates are in timestamps?
For example: one expire = 4073497200
Any idea how to fix this? Thanks for helping me.
Well, timestamps are not used for actual dates and times. That is what the datetime format is used for.
Perhaps you should rethink using them. This is the explanation of timestamps vs datetime that I found for you on StackOverflow. It explains it nicely!
. . . . .
An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. This could be very important if your application handles time zones. How long ago was ‘2010-09-01 16:31:00’? It depends on what timezone you’re in. For me it was just a few seconds ago, for you it may represent a time in the future. If I say 1283351460 seconds since ‘1970-01-01 00:00:00 UTC’, you know exactly what point in time I talk about. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. It is the year 2022 now, 2038 is a near future. What will happen then?
. . . . .
As you see from that explanation, you will run into problems in 2038 if you use timestamps. By then, they will probably have altered the possible values for timestamps, but, who knows. And, also, some servers have issues with calculating the “interval” of numbers of days in timestamp values. Lastly, the time zone can change the way the timestamp is saved. You would need to insure you set your time-zone to the server’s time-zone or your website’s location to lock the time-zone. So, lots of issues using timestamps vs datetime.
Therefore, before testing fixes for your timestamp of 4073497200, can you use datetime format for this field instead? If not we can do some testing on the timestamp. Or maybe just use math… 60 seconds times 60 minutes times 24 hours times 60 days would be 5,184,000. And, you might be able to compare if the current timestamp is within that time.