I have a table from which I'm trying to find rows with datetimes that
fall within a particular period. These might be Today, Monday, This
Week, This Month, August, etc.

My problem is that the report must be from the point of view of local
time, while the datetimes are GMT.

CREATE TABLE adminLogins (
loginTime datetime,
account varchar(20)
);

I don't have much experience. Should I do date arithmetic in the Where
clause? I imagine this problem comes up often, but I wasn't able to
find anything that really addresses it.

If it matters, the server's running MySQL.

Thanks,
-Steve

On 21.09.2006 23:39, Steven Paul wrote:
Yes, it matters. Personally I don't know MySQL. Presumably there are
functions available that deal with this. If you were on Oracle 9 or
newer you could make the column TIMESTAMP WITH LOCAL TIMEZONE and set
your DB session's TZ appropriately. With MySQL you probably have to do
something like

select ...
from ...
where loginTime >= localtime2utc('2006-01-01 ...')
and loginTime < localtime2utc('2006-02-01 ...')

Kind regards

robert

In article <4nhj3aFafil5U1 (AT) individual (DOT) net>, Robert Klemme
<shortcutter (AT) googlemail (DOT) com> wrote:


Thanks Robert,

I realize I didn't compose the question very well. There's a DATE_SUB
function, so if I'm at GMT -6, and the server is at GMT, I could write

DATE_SUB(loginTime,INTERVAL 6 HOUR)

I guess I could use that everywhere I'm dealing with the datetime, but
it's going to lead to some long and messy statements.

My primary concern obviously is to get the right data, but in the past
I've written some naive SQL that resulted in poor performance, and it
feels like I'm heading in that direction again. So I was hoping for
some insight in the right way to handle this presumably common problem.

What I really don't want to do is get more data than I need and filter
it programatically, though I haven't been above doing that before ;-)

-Steve

On 22.09.2006 14:53, Steven Paul wrote:
How does that deal with daylight savings time?

You cannot use that *everywhere* because for select list and criteria
you have to use different conversion directions, i.e.

select gmt2local(column_name) ...
where column_name > local2gmt(your_time_constant)

Depending on your application and whether you need to group by your
local time you could do those conversions on client side as well (that's
pretty easy in Java for example).

I think typically performance does not suffer as long as you make sure
that you apply filters on the raw column values (and not converted
ones). IOW, use the SQL above but not

select gmt2local(column_name) ...
where gmt2local(column_name) > some_gmt_constant

(Although a smart DB might optimize this as well, but I guess it's
considerably harder).

:-) i18n with dates and times is really awful, so sometimes one has to
resort to ugly solutions...

Kind regards

robert

"Steven Paul" <listcatcher (AT) fastOUTmail (DOT) fm> wrote

A couple of things:
1) With some databases allowing Timestamp with Timezone fields and others
expecting conversion in and out of UTC (mainly Sybase/Microsoft databases),
be sure that what you are architecting does not have compatibility with
conversion. There are issues with daylight savings time and also leap
seconds when converting between the 2.
2) To reduce problems with performance on your queries (other than the
obvious indexing etc), ensure wherever possible that any function calls are
applied on the variable field (as opposed to the database field) as applying
the function on the database side will effectively nullify and index on that
field as each value has to be parsed through a function.

AMO

In article <4ni7gnFaiv9kU1 (AT) individual (DOT) net>, Robert Klemme
<shortcutter (AT) googlemail (DOT) com> wrote:


Well, I'm building the query on the fly. I figure out what value to use
there. Here's what I came up with. Kinda ugly, and I don't know how
efficient it is.

The server it's on is fairly limited. I wrote some PHP to construct the
date part of the where clause:

function constructDateClause($columnName, $daysBackStart,
$numberOfDays, $timeZonesToSubtract)
{
$start = $daysBackStart*24 + $timeZonesToSubtract;
$end = ($daysBackStart-$numberOfDays)*24 + $timeZonesToSubtract;

$sql = "to_days(DATE_SUB($columnName,INTERVAL $timeZonesToSubtract
HOUR)) BETWEEN to_days(DATE_SUB(NOW(),INTERVAL $start HOUR)) AND
to_days(DATE_SUB(NOW(),INTERVAL $end HOUR))";
return $sql;
}


If I'm at GMT-6 and I call that as constructDateClause('lastVisitDate',
1, 1, 6)
I'll get back

to_days(DATE_SUB(lastVisitDate,INTERVAL 6 HOUR)) BETWEEN
to_days(DATE_SUB(NOW(),INTERVAL 30 HOUR)) AND
to_days(DATE_SUB(NOW(),INTERVAL 6 HOUR))

Which will give me the rows from today and yesterday, local time.

to_days() is, I think, MySQL-specific. It returns an integer that
represents the number of days since some magic date.

In article <4514373e$0$3614$ed2e19e4 (AT) ptn-nntp-reader04 (DOT) plus.net>, AMO
<NoSpam (AT) SpamFreeWorld (DOT) com> wrote:

Amo,
I'm not following you. What do you mean by variable field?

If write this:

where
to_days(DATE_SUB(lastVisitDate,INTERVAL 6 HOUR)) BETWEEN
to_days(DATE_SUB(NOW(),INTERVAL 30 HOUR)) AND
to_days(DATE_SUB(NOW(),INTERVAL 6 HOUR))

Will it have to be simply brute-forced? FWIW, there's an index on
lastVisitDate.

The MySQL execution plan said
"simple. possible keys: null. Using where; Using filesort"

Steven Paul <listcatcher (AT) fastOUTmail (DOT) fm> wrote:

In this case, the value that you are matching the column value
against.

Probably, since the operations specified must be done on each
column value. Do
select ... from ...
where
datecolumn between
datetransform(rangelow) and datetransform(rangehigh)
instead of
select ... from ...
where
datetransform(datecolumn) between
rangelow and rangehigh

In the first case, datetransfrom() (a placeholder function name)
only needs to be done twice. In the second case, it needs to be done
for each row that might be selected. (Some optimisations might make
it not quite that bad, but this is a general answer.)

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

"Steven Paul" <listcatcher (AT) fastOUTmail (DOT) fm> wrote

Hi Steve,

Gene explained it well in a followup post.

If you had a table that had a field which had the values A,B,C and was
indexed, you could perform a SELECT statement and select a range greater
than B for example.
However, if you ran it through a function such that the values gave 1,10,5
for example, then there are three problems.
1) There is the overhead of calling a function (no matter how small the
overhead).
2) If you are filtering records by the returned value of the function, then
the database does not know whether the record falls within the filter until
every record is parsed through the function. It therefore depends on other
criteria to reduce the recordset before parsing the results through the
function to further reduce the resultset.
3) If you had wanted to sort by the function returned value, you have a
problem. Whilst the records are sorted correctly on the field itself, the
value returned from the function is not indexed and therefore the whole
resultset has to be internally sorted.

If you are using a concatenated index, e.g. lastName > firstName, you really
do not want to be sending lastName or firstName into a function, as you are
testing the value of the returned result and therefore making this index
unusable for this particular query.

Query optimisation can be performed internally by the optimiser. When a
record's value is parsed through a function it can be cached for the
duration of the execution of the query so that if the same value is parsed
again, it already knows what the results should be. However, the query
optimiser can only perform this optimisation if the value returned from the
function will always be the same given the same input values and do not
update any underlying data to the database. The ones that do give the same
return value, e.g. CAPS() (CAPS(f) will always return F) are called
deterministic functions. The ones that don't, e.g. newUniqueDbRecordId()
are called non-deterministic functions.

In essense, don't do things like (structured english):
SELECT * FROM myTable WHERE returnYear(myTable.lastUpdate) = 2006.
Instead do:
SELECT * FROM myTable WHERE myTable.lastUpdate BETWEEN 01/01/2006 AND
31/12/2006.

Try not to perform functions on any field in the WHERE clause or any field
that you wish to ORDER BY. Try doing it on the value you are trying to
compare with or find other solutions.

AMO

In article <451474ef$0$3592$ed2e19e4 (AT) ptn-nntp-reader04 (DOT) plus.net>, AMO
<NoSpam (AT) SpamFreeWorld (DOT) com> wrote:


Sorry for taking so long to reply. I got side-tracked on another
project and just now got back to this one.

I've done as you suggested. I now do all the calculations first, and
then produce a straight-forward SELECT with no function calls in the
WHERE clause.

Thanks for your help!
Thanks also to the others who responded (Gene Wirchenko and one more
whose name I've lost.)

I appreciate the patience with my undoubtedly basic questions.

-Steve