[thelist] SQL Date formating
rudy
r937 at interlog.com
Tue Feb 11 11:38:06 CST 2003
> SELECT *
> FROM 100420_gmtr_t
> where read_date
> like 'DATE_FORMAT(12/01/2002, %Y-%m-%d)%'
hi chris
dates are tough, eh
i assume you are using LIKE in order to truncate times
one reason it's not working is because the string 'DATE_FO...' will be
interpreted as just that very string, i.e. the DATE_FORMAT function isn't
evaluated
but that's not all
i see that Beam has already given you the answer, but hey, it took me a
while to write this so i'm going to send it anyway
this will be a long explanation, so you may want to grab a coffee
;o)
the internal representation of a datetime value is usually a pair of
integers, one for the date (in days) and one for the time (in seconds or
milliseconds)
i believe mysql is the same as other database systems in this regard (there
is uncertainty because mysql allows "certain 'not strictly' legal date
values, for example 1999-11-31")
databases have complex logic for evaluating date expressions, and converting
between internal integer representations and external string representations
in date comparisons, the database can do integer comparisons internally, and
this is generally faster, especially if each row's value does not require
converting
in the type of condition you have, i think all databases would be forced to
do a string comparison, since you are using LIKE
the database thinks "oh, okay, i'll convert read_date to a string, no prob"
so it converts the internal datetime value of read_date to a string, using
its default date formatting algorithm, since you did not explicitly say what
format to use for read_date
so it might come out as 2002-01-12 or some other format which doesn't match
the LIKE string you provided, even assuming you took the outer quotes off so
that DATE_FORMAT could create its string
so you don't select any rows at all
<tip type="sql">
guessing which default date format the database will use is a crap shoot
</tip>
now look at Beam's approach --
where DATE_FORMAT(read_date, %Y-%m-%d)
= '2002-01-12'
this explicity converts into a string only the date part of read_date, i.e.
not the time, which means you do not have to mess around with LIKE, and can
use an equality test instead
notice it's a string comparison so your literal '2002-01-12' has to have the
dashes because that's how read_date is being formatted
as i suggested, letting the database do an internal comparison rather than a
string comparison is usually the better approach, not only for performance,
but also because the sql to do it is often simpler
and simpler is gooder
the challenge, of course, is to handle the specific requirement
your example seeks all read_date values for a specific date
a first attempt at a date comparison might be
where read_date = '12/01/2002'
this will almost always allow the database to decide to convert the literal
into the internal representation of a datetime value
as an aside, the database is pretty good at this, because this is the way
that you normally provide date values, for example in the INSERT
statement --
insert into foo ( bar , somedate )
values ( 'heh' , '2003-02-11' )
i think mysql wants to have the year always stated first in date literals,
so keep that in mind, yours was the wrong format
however, there still may be a problem that will affect your particular
situation
if read_date is a datetime field, and your application allows datetime
values to be inserted with time parts, then your query may "work" but
not deliver what you want
so in this first attempt --
where read_date = '2002-01-12'
when the literal is converted to a datetime value, it will invariably get a
time part of midnight
if read_date contains dates and times, such as it would if you were stamping
rows with date and time of insertion or update, the only rows you'd get back
would be rows where the time part just happened to be midnight
you could design the application so that all datetime values have no time
part (in mysql i believe you could use the non-standard DATE datatype for
this purpose), but this is hard to do as a retrofit
what you can do is write sql that compares just date parts
in this regard, the Beam solution above, which works, i hasten to point out,
might not run very well because it is a string comparison and requires that
each row's read_date value be converted
an alternative approach is to utilize a builtin function, if one exists, to
extract just the date part of a datetime value
an example i have seen in mysql is
where to_days(read_date) = to_days('2002-01-12')
this explicitly converts the date to an integer
recall that the internal representation of a datetime value has the
actual day number as the first integer
thus -- and i'm guessing here, but it's an educated guess -- there is no
actual conversion of read_date values required
the expression to_days('2002-01-12') would of course be converted once, at
the time the query is parsed, and then the comparison could proceed on
read_date with no conversion
i'm not certain that mysql works that way, i merely hope that it does
this might seem like splitting hairs, but careful consideration of sql can
be very important in dealing with large tables
keep this sensitivity to the date conversion issue in mind in any query that
deals with date expressions
foir example, if you write
where year(read_date) = 2002
this allows the database to recognize this as an opportunity to
compare the days integer to bounding values -- the optimizer would
create a day integer for the first day of 2002, and another for the last day
of 2002, and then do a BETWEEN test on the date part of read_date, in order
to optimize retrieval by avoiding conversion, because the date part of the
read_date column is already a days integer
if on the other hand you said
where DATE_FORMAT(read_date,'%Y') = '2002'
then a conversion must be done to do the explicit string comparison
as i said, dates are tough, eh
rudy
More information about the thelist
mailing list