my kingdom was lost.

Watch those commas. A little SQL detail that can bite you:

SELECT
SID
NAME,
ETC
FROM MYTABLE

MySQL, and presumably other databases, is just fine with that statement. It’ll alias the field SID to NAME. If you name columns explicitly with the AS keyword, a missing comma will yield an error:

SELECT
SID AS studentid <--baaarf
NAME AS studentname,
ETC AS studentetc
FROM MYTABLE

I fix bugs for a living. I found this doozy recently. Get out your sticky yellow pad and write this down:

Don’t do this:

select now() - max(my_timestamp) from my_table;

Go find a table with a TIMESTAMP field in it.

mysql> select my_timestamp from my_table limit 1;
my_timestamp: 2006-02-15 02:22:15
1 row in set (0.00 sec)

Looks nice. Now try:

mysql> select max(my_timestamp) from my_table;
max(my_timestamp): 2007-01-31 22:39:17
1 row in set (0.35 sec)

Now this:

mysql> select max(my_timestamp)+0 from my_table;
max(my_timestamp)+0: 2007
1 row in set (0.35 sec)

What happened? max(my_timestamp) was evaluated in a numeric context. The timestamp string was truncated at the first non-numeric character.

Something a bit different happens to now:

mysql> select now();
now(): 2007-02-06 22:47:25
1 row in set (0.00 sec)

mysql> select now()+0;
now()+0: 20070206224744
1 row in set (0.00 sec)

So the original exssion now() – max(my_timestamp) will yield

20070206224744 – 2007

which I doubt is what you had in mind.

If you need to calculate the difference in days, try:

to_days(now()) - max(to_days(my_timestamp))

For something a bit more fine-grained, try:

select (unix_timestamp(now())- unix_timestamp(max(stamp)))

offered his computer skillz in return for a feel. The craigslist community voted this goober off the island.

Nice try, l@m3r, but we got skillz of our own.

When your code needs an extra set of eyeballs

My name is Liz, and I’m a Perl Gerl. I live in Santa Barbara, CA with my family and work for ValueClick Media. When I can manage the drive, I like to attend Thousand Oaks Perl Mongers in Westlake Village and LA Perl Mongers in the heart of the Sunset Strip. Previously I lived in Chicago, IL, and am an emerita of Chicago Perl Mongers.

Besides hacking Perl, I like to surf the waves and the net, cook and bake, knit and read.