Time Calculations 1

[MichiganRon]

I have a table that contains data which expires after a certain period of time. I have a PHP script which runs a query to delete records older than a certain date/time. This date/time is given in unix timestamp format.

The table has a field of "datetime" format named "starttime". I want to do something like this:

DELETE FROM messages WHERE starttime < $expiretime

Unfortunately when I do this, it doesn't work because the query appears to pull the data from the starttime field in a different format than unix timestamp.

I know that there are many different date/time functions that can be used in MySQL sql statements. I am looking for the most efficient way to compose this query.

Any help would be greatly appreciated.

“If you are irritated by every rub, how will you be polished?” ~ Mevlana Rumi


Do you live in Michigan? Join us in the Tek-Tips in Michigan forum.

 

[MichiganRon]

Oh yeah, I forgot. I have a follow up question:

Would it just be more efficient for me to convert my (datetime) field to (int)? I could then store my Unix timestamps and perform calculations with simple arithmetic.

I'm dealing with millions of records, so I'm looking for the most efficient way to do this, not only from a query standpoint, but from a disk space standpoint too.

But if I had to rate importance, query efficiency would be the top priority.

Thanks again!
Ron

“If you are irritated by every rub, how will you be polished?” ~ Mevlana Rumi


Do you live in Michigan? Join us in the Tek-Tips in Michigan forum.

 

[MichiganRon]

Um, okay...one more thing: The $expiretime is calculated by NOW() - n days. Maybe doing the calculations within the query would work?

“If you are irritated by every rub, how will you be polished?” ~ Mevlana Rumi


Do you live in Michigan? Join us in the Tek-Tips in Michigan forum.

 

[towerbase]

I would have thought that the MySQL UNIX_TIMESTAMP function would be the best solution. I have no idea how fast it is but an SQL statement along the lines of

DELETE FROM messages WHERE UNIX_TIMESTAMP(starttime) < $expiretime

should be suitable.

You would save disk space and speed up the process by holding the starttime column as an int but only you can determine if saving a few seconds is worth the effort involved. I always aim to keep things as simple (and as correct) as possible.

Andrew
Hampshire, UK

 

[MichiganRon]

Thanks!

I'm modifying the schema and code to the field type of int. It'll take a while to update all of the records, but it will be worth it in the long run.

I have a question, though. Why would anyone want to use the datetime field type if it is quicker and takes up less disk space to use the int field type? I mean, what advantages are there to the datetime field type, other than being human readable?

Ron

“If you are irritated by every rub, how will you be polished?” ~ Mevlana Rumi


Do you live in Michigan? Join us in the Tek-Tips in Michigan forum.

 

[towerbase]

The int date is the number of seconds after the Unix epoch date which is 1/1/1970 . If you are using 32 bit integers, then the maximum date it can store is 19 Jan 2038.

MySQL DateTime fields can hold most dates that you are likely to want to include in a database.

For many applications that is a good enough reason to avoid using ints to hold dates.

The Y2038 problem might turn out to be more significant than the Y2000 problem!

Andrew
Hampshire, UK

 

[MichiganRon]

Thanks for the info. A star for you.

FYI: I changed the schema of my database so that all of the date/time fields are now (int). I don't think I need to worry about the Y2038 problem with this system. If it's still in use at that time, I'd be shocked (and nearly retired ).

Ron

“If you are irritated by every rub, how will you be polished?” ~ Mevlana Rumi


Do you live in Michigan? Join us in the Tek-Tips in Michigan forum.

 

[towerbase]

Thanks for the star!

It would be interesting to find out what performance benefit you get from changing date formats.


Andrew
Hampshire, UK

 

[r937]

what advantages are there to the datetime field type, other than being human readable?

well, next time you're in phpmyadmin, browsing rows, ask yourself whether being human readable isn't worth something after all

also, datetime values are more portable, should you decide to move the app to a different database system

happy 1167662220, and have fun on 1172673420



r937.com | rudy.ca