PHP/MySQL question, table names and strtotime()

[AngryJim]

I'm trying to design a website but running into a problem. I've been using tables in MySQL named in the format (year_month_date) so today would be 2009_01_25. Problem is, strtotime("2009_01_25") won't return a proper timestamp. I changed some tables over to the format (year-month-date) which works fine with strtotime(), however now all my MySQL queries are broken. Is there something about using a table name like 2009-01-25 that would break MySQL queries?

I'm sure I could use both formats and use preg_match to swap back and forth, but I'd rather not complicate things and add the additional overhead.

EDIT: I'm playing around with a few things now and it seems changing the table format to (yearmonthdate) ie: 20090125 works fine with both strtotime() and the table names, but I think the 2009-01-25 makes for a much cleaner, easier to read url if it can be used.

 

[mikeblas]

Why do you want one table per day? That sounds like a disaster of a data model. Are you planning on partitioning?

Minus is a token, so it's possible that MySQL doesn't think you've got a valid table name. You can quite the name in a backtick, like `YourName2009-01-25` so that it is recognized by the parser correctly.

 

[AngryJim]

Actually it's been working great so far. The site stores information from a variety of sources on a daily basis, and then lets people go back and look it up later. I have a cron job in php running nightly that deletes any tables older than 30 days and also creates new tables for 30 days out.

The reason I am changing things is that my URLs are pretty ugly and very dynamic, using the UNIX timestamp as a $_GET value. I've made some changes to the .htaccess file to get more SEO friendly links. I just think /2009-01-25 looks better than /20090125.

I think I've come up with a solution though. Thanks for letting me know that "-" is a token and therefor is causing the problems.

 

[XOR != OR]

Not to thread jack, but I've come across a few instances where having a table per day has been beneficial for me too. However, given your comment Mike, that would appear to be the wrong way to do it. How else would you suggest it? I've got a ton of data who's most important attribute is the day ( squid logs, parsed in to a database ). I want to retire the data out of the database after a fixed amount of time ( as Jim appears to be doing ), however given the sheer volume of data running a query to parse through a single database would take hours if not days after a month's worth of data is collected. The best solution I've found has been to drop the logs into tables named by the dates and drop the tables when I'm done with them.

What would you recommend for that kind of setup?

 

[heatlesssun]

It sounds like to me that you guys are not thinking in relation database terms. In most database models you would have an indexed column such as CREATE_DATE or whatever you want to call it. In your queries you would do something like:

SELECT * FROM YOUR_TABLE_NAME WHERE CREATE_DATE = '01/23/2009'

To dump the rows

DELETE FROM YOUR_TABLE_NAME WHERE CREATE_DATE = '01/23/2009'

Relational databases when indexed properly don't actually need to do row by row scans of your tables. There are advanced capabilities in Oracle and SQL Server that let you actually partition a database in the case of very large databases, but at least in Oracle and SQL Server you really need tens of millions rows and tons of activity to really need do something like. I don't know enough about MySQL to comment on partitioning.

 

[mikeblas]

I've made some changes to the .htaccess file to get more SEO friendly links. I just think /2009-01-25 looks better than /20090125.

Are you appending text from the URL into your SQL statement?

Not to thread jack, but I've come across a few instances where having a table per day has been beneficial for me too. However, given your comment Mike, that would appear to be the wrong way to do it. How else would you suggest it? I've got a ton of data who's most important attribute is the day ( squid logs, parsed in to a database ).

The relationally correct way is to have a table with a date column. This way, it's trivial to write queries that need to span data from multiple days.

Partitioning the tables across days has advantages, but those advantages have to be compared to the idea of making it harder to write queries. Being able to manage table-by-table data, just dropping a table as needed, is useful. If your database management system can handle it, creating a view over the set of active tables you've got can make writing key-spanning queries simpler, but you have to buy-in to the maintenance of the view as well as the tables.

Calling it a "nightmare" was probably an overstatement, as there are valid reasons to implement it this way. (I'm about to partition a table at work, here; it's about 400 million rows.) Thing is, the need to easily write wider queries usually carries more weight.

 

[heatlesssun]

Are you appending text from the URL into your SQL statement?



The relationally correct way is to have a table with a date column. This way, it's trivial to write queries that need to span data from multiple days.

Partitioning the tables across days has advantages, but those advantages have to be compared to the idea of making it harder to write queries.

In modern databases partitioning is transparent to queries which is the beauty of it. It takes some thought to do it right and also with the power of relational databases and hardware these days you really have to have a very large and/or very transactional system. If one person is able to handle this system, I really doubt that the partitioning is necessary.

Probably the only thing you need are some good indexes. You might find once you've created good indexes that a one table solution is even faster than multiple tables.

 

[mikeblas]

Partitioning can be transparent to the queries -- once the partitioning system has been arranged and the queries have been written and tested. The performance will depend on the queries involved.

 

[XOR != OR]

It sounds like to me that you guys are not thinking in relation database terms. In most database models you would have an indexed column such as CREATE_DATE or whatever you want to call it. In your queries you would do something like:

SELECT * FROM YOUR_TABLE_NAME WHERE CREATE_DATE = '01/23/2009'

To dump the rows

DELETE FROM YOUR_TABLE_NAME WHERE CREATE_DATE = '01/23/2009'

Relational databases when indexed properly don't actually need to do row by row scans of your tables. There are advanced capabilities in Oracle and SQL Server that let you actually partition a database in the case of very large databases, but at least in Oracle and SQL Server you really need tens of millions rows and tons of activity to really need do something like. I don't know enough about MySQL to comment on partitioning.

I'm working with mysql so it's possibly a deficiency with it; however I did originally design the tables correctly the first time around. Deleting a day's worth of data from the database would cause data loss as I was still trying to write to the same table, nevermind that it brought the entire system to it's knees as well as taking most of the day to accomplish.

The relationally correct way is to have a table with a date column. This way, it's trivial to write queries that need to span data from multiple days.

Partitioning the tables across days has advantages, but those advantages have to be compared to the idea of making it harder to write queries. Being able to manage table-by-table data, just dropping a table as needed, is useful. If your database management system can handle it, creating a view over the set of active tables you've got can make writing key-spanning queries simpler, but you have to buy-in to the maintenance of the view as well as the tables.

Calling it a "nightmare" was probably an overstatement, as there are valid reasons to implement it this way. (I'm about to partition a table at work, here; it's about 400 million rows.) Thing is, the need to easily write wider queries usually carries more weight.

I think, possibly, we're talking about different scales.

But I appreciate the information, certainly.

 

[mikeblas]

Deleting a large amount of data out of a single table has the chance of promoting row or page locks to full table locks, so that the table isn't accessible at all while the large deletion is taking place. You should code your application to behave appropriately in the face of time outs or deadlocks.

Depending on how partitioning is implemented in your database server, you might have a similar problem with it, however. For example, say your master table is a view of unions of the smaller tables. When you drop an individual table, you'll need to update the view definition. Anything touching that definition will temporarily not work, or be locked on the view's metadata operation. For more advanced implementations of partitioning, you might have better luck, or you might be able to tuck the changes into downtime, or a rename operation, or what have you -- and find a workaround.

 

[AngryJim]

Ok I've gotten the whole thing figured out, but ran into a problem. My URLs now end with /Jan-25th-2009 as an example. The php uses strtotime() to turn it into a timestamp and then uses the timestamp from there. For some reason, everything from 2008 or 2010 returns a timestamp from the year 2009. To test, I just wrote this up really quickly and threw it on the server:

<?php
date = "Jan-25th-2010";
$timestamp = strtotime($date);
echo	$timestamp;
echo	'<br />';
echo	date('l, F jS, Y', $timestamp);
echo	'<br /><br />';
$date1 = "Jan-25th-2009";
$timestamp1 = strtotime($date1);
echo	$timestamp1;
echo	'<br />';
echo	date('l, F jS, Y', $timestamp1);
echo	'<br /><br />';
$date2 = "Jan-25th-2008";
$timestamp2 = strtotime($date2);
echo	$timestamp2;
echo	'<br />';
echo	date('l, F jS, Y', $timestamp2);
?>

Which gives the results:

1232914200
Sunday, January 25th, 2009

1232914140
Sunday, January 25th, 2009

1232914080
Sunday, January 25th, 2009

The three timestamps are 60 seconds apart, can't figure out why. So basically, everything works perfectly for all 2009 values, but 2008 and 2010 values are broken. I might move to using Jan/25th/2009 at the end of the url if that would be easier to work with, but It would be easier if I could just fix what I've already written.

 

[AngryJim]

Are you appending text from the URL into your SQL statement?

I'm guessing you're asking for security reasons. It passes a variety of checks to make sure it's valid data first. Even then, since I can't get a system that will match what's in the URL to what goes in the SQL, it gets converted to a timestamp with strtotime() and then into a usable format for the query with date().

 

[mikeblas]

<?php
date = "Jan-25th-2010";

You're missing a dollar sign there.

The three timestamps are 60 seconds apart, can't figure out why. So basically, everything works perfectly for all 2009 values, but 2008 and 2010 values are broken. I might move to using Jan/25th/2009 at the end of the url if that would be easier to work with, but It would be easier if I could just fix what I've already written.

The problem is the "th", which isn't expected and apparently not supported by strtotime(). This code:

<?php
$date = "Jan-25-2010";
$timestamp = strtotime($date);
echo	$timestamp;
echo	'<br />';
echo	date('l, F jS, Y', $timestamp);
echo	'<br /><br />';
$date1 = "Jan-25-2009";
$timestamp1 = strtotime($date1);
echo	$timestamp1;
echo	'<br />';
echo	date('l, F jS, Y', $timestamp1);
echo	'<br /><br />';
$date2 = "Jan-25-2008";
$timestamp2 = strtotime($date2);
echo	$timestamp2;
echo	'<br />';
echo	date('l, F jS, Y', $timestamp2);
?>

produces the results you expect:

1264406400<br />Monday, January 25th, 2010<br /><br />1232870400<br />Sunday, January 25th, 2009<br /><br />1201248000<br />Friday,
January 25th, 2008

My guess is that strtotime() sees the "th", falls off, assumes the current year, and then takes "2009" or "2010" or "2008" as a 24-hour time format.

 

[AngryJim]

Actually, strangely enough, I removed the "-" and now all dates are done as /Jan25th2009 which works just fine for all values I tested with.

Thanks for all the help.

 

[mikeblas]

Nothing strange about it; you've just found another combination of tokens that strtotime() happens to accept. This seems like a pretty miserable function, as there's no documentation about exactly which string formats are accepted or how they're interpreted. I guess you have to try things to see what works, then stick with them when they do.