Evening

I'm playing around with dates and MySQL at the moment and I've been sat
here for hours going round in circles. I have a date column in a table
that is written in the format:

yyyy-mm-dd

What I need is a MySQL query that will pull out all records older than
30 days.

I used to do all this with microtime and it was a piece of pish.
Someone persuaded me to go down a different route for dates with this
and I'm now stuffed!

I've read and re-read this:

http://dev.mysql.com/doc/refman/4.1/...culations.html

But my answer still eludes me.

Can someone throw some light in it please.

Cheers

A

--
Andy Jacobs
www.redcatmedia.net
Intelligent Websites For Intelligent Business People

On Sat, 28 Jan 2006 00:16:12 +0000 (UTC), Andy Jacobs put finger to
keyboard and typed:

There are plenty of options. Here's one:

SELECT *
FROM mytable
WHERE TO_DAYS(column_with_date) < (TO_DAYS(NOW() - 30)

What this is doing is converting the date column to a value in days,
then comparing it with today's value in days minus an offset.

Here's another one:

SELECT *
FROM mytable
WHERE column_with_date < DATE_SUB(NOW(), INTERVAL 30 DAY)

This is selecting everything where the date column is older than the
date obtained by subtracting 30 days from now. The nice thing about
this way of doing it is that the INTERVAL type can be various other
values such as MONTH or YEAR, which is handy for getting dates older
than a calendar month rather than a fixed number of days. This will
select anything older than a calendar month:

SELECT *
FROM mytable
WHERE column_with_date < DATE_SUB(NOW(), INTERVAL 1 MONTH)

This is over a calendar year old:

SELECT *
FROM mytable
WHERE column_with_date < DATE_SUB(NOW(), INTERVAL 1 YEAR)

And, assuming that you've got a datetime value (ie, in the form of
yyyy-mm-dd hh:mm:ss) rather than just a date, you can do things like
this:

SELECT *
FROM mytable
WHERE column_with_datetime < DATE_SUB(NOW(), INTERVAL 20 MINUTE)

This flexibility with date and time calculation is one of the reasons
why it's better to store dates and times in date and datetime columns
rather than converting everything to microseconds and storing it as an
integer - it means you can make the SQL do the work of calculating
differences rather than having to script it yourself.

Mark
--
http://www.MotorwayServices.info - read and share comments and opinons
"Sing for the laughter, sing for the tears"

On Sat, 28 Jan 2006 07:57:38 +0000, I put finger to keyboard and
typed:

I should just add that this (and the rest of my post) assumes that the
column containing the dates is actually a datetime or date column,
rather than char or varchar. If it isn't, then convert it to one -
doing numeric calculations on dates saved as strings is only for the
severely masochistic.

(Although it doesn't work the other way round - you can't use datetime
functions on trings - you can treat a datetime value as a string if
you want to. For example, "where date_column like '2005-11%'" will
select everything from November 2005. This can be useful if you want
to pass in a human-generated variable, sometimes).

Mark
--
http://www.MotorwayServices.info - read and share comments and opinons
"Would you save my soul, tonight?"

Mark Goodge wrote:

It's not that hard to do oneself, if you use whatever date/time
library functions are provided by your programming language.
In fact, just as you demonstrated with MySQL's date/time.

The situation where you might wish to do it that way is when
your code is designed to be portable across different SQL
backends, and you don't want to rely on SQL's woefully
unstandardised capabilities.

--
Nick Kew

On Sat, 28 Jan 2006 10:22:44 +0000, Nick Kew put finger to keyboard
and typed:

Sure. You can do pretty much anything with the programming language
that's using the data extracted from MySQL, and plenty of them have
very good functions for handling dates. And there are occasions when
handling the calculation in the code is better than doing it at db
level; sometimes you want to reuse a variable in different contexts
and it's more helpful to extract it "raw" from the db than pre-select
it. But the OP was specifically asking for a MySQL query to do the
job, rather than a code snippet in PHP, Perl or whatever. And that's
what I'd call masochistic: Writing an SQL query to do numeric
comparisons on dates stored as strings.

That's true, but fully portable code like that tends to be rather
baroque. It's not just a case of sticking to basic SQL, you also need
the code to run different functions to access the db - in PHP, for
example, you'd need to have a function to check what the db is and
then use, for example, pg_*, ora_* or mysql_* functions as appropriate
in order to get at the data. That means a lot of redundancy in the
code, which isn't usually a desirable outcome. There aren't many
situations where that level of portability is necessary - it's usually
easier to have different versions of the script for different dbs, or
just rely on one common one being available.

Mark
--
http://www.OrangeHedgehog.com - RSS feeds and Google Adsense tools
"Life is both a major and a minor key"

Mark Goodge wrote:

No, that's the easy part. If you use Apache 2.2, the portable C
layer is all there as standard. Site Valet has had that for some
years, and is waiting for me to find time to upgrade it to using
apache's native DBD layer. Perl has had its DBI/DBD for over a
decade.

It's the differences in SQL that are more challenging to work with.

--
Nick Kew