Please how else can i delete rows whose datetime is older than 5MINs?
I know of using
DELETE FROM table WHERE date < (NOW() - INTERVAL 5 MINUTE)
But the problem am having is that mysql datetime column saves a time that is 1hour below my php time.
So bcs of it i started using date(‘Y-md H:i:s’) to update the columns because i want the dates in php to match mysql data
But how ever if i run the above date deleting query all rows are cleared because they are over 1hour.
And am not using current_time function on my mysql datetime columns because i what dates that is source and produced by php.
For the sake of using the script in any timezone, without having to configure clients database time because of my script.
Please how can i work this out?
In my own i can do minus 1hour 5mims so that it works with my php time, but i wont be at my clients site or database to know which timezone and what to minus or add
Thirdly, what i had in mind is to get strtotime() and then minus 300 secs from but then how do i convert the time back to Y-m-d H:i:s as to then use it in mysql instead of using INTERVAL 5 MINUTE
I can just say WHERE date < $myreducedfiveminutes
But which time are you going to use? You’ve already said that your PHP time isn’t the same as your database time, so won’t this just have the same problem?
If you changed the insert… you need to change the delete, as well.
Well now you’re changing the statement. Either you want everything that is more than 5 minutes old, or you want everything that is an hour and 5 minutes old.
Timezone doesn’t matter as long as your reference point doesnt change. “5 minutes ago” is 5 minutes ago in whatever timezone, if you’re looking at the same clock both times you check.
the date function takes a second optional parameter. Have you tried using it?
1 Like
Thanks @m_hutley but i was not trying to change it, what i wanted was 5mins difference but because or timezone using NOW() i made it 1hr plus 5 as to accommodate the time behind.
Thanks, @droopsnoot
But i don’t care what time my database uses, i am only using php time for all my date inserting queries in the database.
But my problem is using INTERVAL 5 MINUTES which i suspect is only available for NOW() in database.
I tried it that way but mysql shouted at me.
This was exactly what i did
$phpdate = date('Y-m-d H:i:s');
DELETE FROM table WHERE date < ($phpdate - INTERVAL 5 MINUTE)
I Wanted to replace the NOW() which produces a date that is 1hr ahead of mine, with the phpdate variable i created but it didn’t work
MySQL cant do date math on a string.
$phpdate = date("Y-m-d H:i:s",strtotime("5 minutes ago"))
DELETE FROM table WHERE date < $phpdate (though you should be using prepared statements…)
1 Like
Yes am using prepared statements,
Thanks i will try the code once i on my laptop and then give you feed back.
And i think i barely know the power of strtotime() when it comes to date, i think i should start taking that dude very serious.
I will go read it up.
Let the Database do the work.
DELETE FROM table WHERE dateColumn <= DATE_SUB(NOW(), INTERVAL 5 MINUTE);
Thanks Sir @benanamen but we are not stopping it from doing its job, we just don’t like its timezone. Is not in line with our php timezone, so we used @m_hutley method
That strtotime() is a life saver, i can run timed functions in my script using it.
The beauty of it is that it produces 2020-10-12 if used like this date(‘Y-m-d H:i:s’, strtotime(‘5 mins ago’));
It’s one way or another; either have PHP do all the work, or the database do all the work; just keep the frame of reference the same. Otherwise you have to be sure your database and webservers have the same (or at least, within tolerance) clocks.
1 Like
What time zones are in use in the database and in PHP?
Short answer…
User sets their timezone in the app.
App converts the time to UTC and stores UTC time data in DB
Retrieving: UTC time is converted in app to users saved timezone setting, etc, etc…
2 Likes
Exactly i allow php do all the date for now, as to have a uniformed time and date throughout the site.
Is there a way to convert 1605435321 unix time back to text 2020-10-12 20:15:23
But, but, it’s not uniform. How do you know what timezone the datetime is in the DB? It could be any number of timezones.
What if the user moves and has a new timezone? You will end up with problems you never even thought of. The direction you are taking is not scalable at all.
You need to store ONE central time (UTC- Coordinated Universal Time) and then convert that uniform time to what ever is needed by the end user.
1 Like
Doesn’t matter for a delete operation, as long as the frame of reference doesnt change.
The database, when told the time is X/Y/Z H:i:s, has no concept of the field being ‘now’. It simply stores that datetime in its local frame.
When the delete command tells it to delete from R/S/T J:b:l, the db has no concept of it being ‘5 minutes ago’, it simply performs the operation it was told to do, and dutifully, and correctly, deletes the targetted records.
The problem comes in retrieving and displaying the data from the database; as a timestamp would return the timestamp in nontimezone format, and the date field may return it as a string representation of the date object, which would then need reckoning.
the second parameter of date takes an integer timestamp (which is what strtotime does - renders a string into its representative integer timestamp.)
1 Like