Dear Readers,
I have a little mySQL problem here.
Maybe it's a bug, maybe I'm just too stupid to read the documentation
correctly, who knows... I'm sorry if I bother anyone with my stupidity.
I run a simpy query like:
SELECT INTERVAL((UNIX_TIMESTAMP(ReceivedDateTime) -
UNIX_TIMESTAMP(StartDateTime) - 86400) / 60, 360, 720, 1440, 2880,
5760) FROM Station, File WHERE File.StationId=Station.StationId ORDER
BY File.FileId
First I calculate the time between two DATETIME fields (in seconds),
then I substract 84600 (that's the number of seconds a single day has)
and divide the rest by 60 to get minutes (is there an easier way to do
this? Let me know please).
In the end I get a time difference in minutes that I want to compare
with a set of numbers, that's what I use the INTERVAL for. If there are
less 360 minutes, it should return 0, if there are less than 720 it
should return 1, ... and if there are more than 5760, it should return
5. And guess what, that's exactly what it does! :-)
But the result is sorted by the FileId. I don't want it to be sorted by
the FileId of the file, but by a Station ID that consists out of four
characters. So I modify the query like this:
SELECT INTERVAL((UNIX_TIMESTAMP(ReceivedDateTime) -
UNIX_TIMESTAMP(StartDateTime) - 86400) / 60, 360, 720, 1440, 2880,
5760) FROM Station, File WHERE File.StationId=Station.StationId ORDER
BY Station.FourCharacterId
It's exactly the same query as before, I only altered the sort order,
so I expect the same result, just in a different order. But it's not
the same result at all.
All of a sudden the INTERVAL function seems to always return 3, because
all rows have 3 as result. WHY? :-/ I just changed the search order, so
how can it have such a big influence on the result? And how can I get
the desired order and still get the desired result for each row? Can
anyone shine a light on this issue please?
--
Best Regards,
Markus Hanauska
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
