timestamp issues

jasondavis

I have been using the DATETIME feature for mysql for a while and I am now wanting to change my site to store date/time as a timestamp in a regular INT I have read that this will be better for performance and it seems it will since most of my time/date functions basicly take this vaue from the DB 2009-06-30 18:10:48 and translate it to a timestamp to messure time or change for timezone.

1 thing I am worried about is I currently use many things like thisd currently with datetime, this is a mysql query that deletes some records that are 10 days old, I love how simple it is to get records X amount of days old, how would I do the below code if there is no more datetime and it is stores as a regular timestamp in an INT?

delete from friend_bulletin where TO_DAYS(NOW()) - TO_DAYS(submit_date) >=10

Bjom

I've read this article on the topic and here is the conclusion:

"Anyway, what I’ve tried to demonstrate was usage scenarios that you’ll need to consider for your own real cases: INT remain smaller in storage (50%) and will only perform better if INSERTs and SELECTs are already fed with an INT value - and this is specially relevant for WRITE-intensive scenarios - but DATETIME alleviates extra responsability/care from the developer. Programmers don’t usually care about this, and want the most flexibility from the database, so it’s up to you to find with them a compromise. I may have provided both enough arguments for an endless discussion, though"

However, what kind of DB are we talking about in your case? Do you have the thousands of writes and reads and millions of records, that justify switching from DATETIME to INT? Are you sure you don't produce a lot of overhead when you start date comparisons using ints?

johanafm

I agree with Bjorn. You should really consider your options. However, if you always find yourself casting from date to int, then of course you should go ahead.

As for the SQL, it's simple. Get rid of the TO_DAYS(), and switch 10 days to the same time in seconds:

DELETE FROM friend_bulletin WHERE NOW() - submit_date >= 10 * 24 * 3600
-- And of course, you should replace the mathematical expression above with: 864 000

Weedpacket

Although you should note that not all days are 86,400 seconds long (it's possible for Sunday+86400 seconds to still be Sunday).

johanafm

If you do need to care about that, it can be resolved by using a leap second lookup table though.

Weedpacket

No, because that would only handle the cases of days that are 86,401 or 86,399 seconds long, where in fact some are 90,000 or 82,800 seconds long.

johanafm

Good point. I've actually never thought about what happens when going to/from DST. Ignorance is bliss. Now, I suddenly have a good deal of old code to go through and check for potential problems.
Still, thanks for the (time consuming) feedback 😉