Consider a table that holds Internet browsing history for users/machines,
date/timed to the minute. The object is to tag all times that are separated
by previous and subsequent times by x number of minutes or less (it could
vary, and wouldn't necessarily be a convenient round number). This will
enable reporting "active time" for users (a dubious inference, but hey).
There are a lot of derivative ways of seeing this information that might be
good to get to. What's the fist and last of these sets of times? What
percentage of a given period is spanned by active times, and not? What is
the average duration of such periods? What is the average interval between
web hits during such periods? During other times?
Blah, blah. The basic problem is my principal problem. I don't have much
experience with cursors, but from what I understand it would be very good
indeed to spare them, given the number of records I anticipate working
with.
I'd be glad of any pointers.
--
ScottBasics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.|||There are many way to accomplish this, for instance:
get a set of beginnings of active periods:
select ...
from events where not exists(
-- no events in the preceding ... minutes
)
and exists(
-- events in the followinging ... minutes
)
get a set of endings of active periods:
select ...
from events where not exists(
-- no events in the following ... minutes
)
and exists(
-- events in the preceding ... minutes
)
That done, you need to match every beginning to its corresponding end.
This is very simple using row_number() available in SQL 2005
In earlier versions, you can either emulate row_number() using
identity() column in a result set, or use a join condition like this:
...
from beginnings b join ends e on b.time<e.time
where not exists(select 1 from beginnings b1 where b.time< b1.time and
b1.time<e.time)
and not exists(select 1 from endings e1 where b.time< e1.time and
e1.time<e.time)|||On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
> Basics: Time comes in durations, so evetns have a start and stop time.
> The really good stuff can be found at the University of Arizona
> website where they have a PDF copy of the Rick Snodgrass book and his
> research paper.
Joe, without passing judgement on your basic assertion "Time comes in
durations", you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants. Unless you
intend to win over the writers and administrators of every web server on
the planet, we're going to have to damn well DEAL with them as events, not
durations.|||Ross Presser opined thusly on Aug 30:
> On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
>> Basics: Time comes in durations, so evetns have a start and stop time.
>> The really good stuff can be found at the University of Arizona
>> website where they have a PDF copy of the Rick Snodgrass book and his
>> research paper.
> Joe, without passing judgement on your basic assertion "Time comes in
> durations", you must be aware that web requests, like most other events in
> computing, are not ever logged as durations, but as instants. Unless you
> intend to win over the writers and administrators of every web server on
> the planet, we're going to have to damn well DEAL with them as events, not
> durations.
Well, OTOH the telos of the click is to digest content, which consumes time
(duration). But we don't have eyeball trackers on our desktops yet, so
we're left to infer from events that there's subsequent eyeball activity --
users don't do http GETs for no reason.
But that's an abstraction -- a problematic one -- whereas indeed these are
events. Still, like vertices on a triangle, to get from one to another of
these moments you have to traverse the length of a side.
Grats to Joe for the sensible reply. I'm always slapping my forehead. Just
now I'm having trouble even with that simplicity. :-/
--
Scott|||"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:1w3cyv7dg377n$.dlg@.rosspresser.dyndns.org...
> On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
> > Basics: Time comes in durations, so evetns have a start and stop time.
> > The really good stuff can be found at the University of Arizona
> > website where they have a PDF copy of the Rick Snodgrass book and his
> > research paper.
> Joe, without passing judgement on your basic assertion "Time comes in
> durations", you must be aware that web requests, like most other events in
> computing, are not ever logged as durations, but as instants. Unless you
> intend to win over the writers and administrators of every web server on
> the planet, we're going to have to damn well DEAL with them as events, not
> durations.
You might as well give up. Joe and I had this argument I think it was a
year ago and he was just as wrong then as he is now.|||AK opined thusly on Aug 30:
> There are many way to accomplish this, for instance:
Thanks. It's been my only clue, and was sure sensible. I thanked Joe
earlier, remiss. However, I owe him thanks for leading me toward that big
paper on temporal SQL -- that stuff's dynamite. It certainly gives an
answer to the duration/event argument: "yes." ;-)
NOW my fun is that the times I'm working with have only minute precision,
so I often get several identical times for a given user (the most logical
grouping). This presents all kinds of problems for the kinds of reporting
I'm looking at. It's fine for determining periods of activity when one's
after a minute mark, but anything beyond that starts getting hairy. Sure
wish I had even second precision. The client-side use of shdoc401.dll
namespace(34) seems to preclude this, sadly.
--
Scott|||>> you must be aware that web requests, like most other events in computing, are not ever logged as durations, but as instants.<<
Do not confuse the recording of the data with the data model. Think
about a sign-in and sign-out sheet or timeclock. Each line is "half a
fact"; the whole fact is the duration spent on the job. In this case,
the user logs onto a site, stays there for x-minutes. He is not there
for a Chronon (that is the term for a point in time in temporal
databases). So his table MIGHT look like this:
CREATE TABLE Browsing
(user_id VARCHAR(30) NOT NULL,
website VARCHAR(255) NOT NULL,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
logout_time TIMESTAMP, -- null means still there
CHECK (login_time <= logout_time), -- less than one minute problem
PRIMARY KEY (user_id, website, login_time));
The real problem in this situaiton is having to round to the minute.
We can force a convention on the stopping time to keep it away from the
starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
to 'yyyy-mm-ddThh:mm:59.997').
Ever read the paradoxes of Zeno? He went thru what happens when you
believe in Chronons. He lived in a time when Greek math was "integer
only" and without a continuum.|||--CELKO-- opined thusly on Aug 31:
> The real problem in this situaiton is having to round to the minute.
> We can force a convention on the stopping time to keep it away from the
> starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
> to 'yyyy-mm-ddThh:mm:59.997').
As in my further woes (see recent reply in thread), at issue is the
adequacy of the data in describing the phenomena In this case, users cannot
simultaneously click a mouse on more than one thing -- though it's not
impossible to identify ways that http GETs can occur concurrently on one
machine under one user's context. At any rate, it should be obvious that
minute precision for web browsing event recording is insanely coarse. But I
doubt anyone planned for local Internet History to be used for purposes I'm
stretching it to. A proxy server is a choke-point that allows for more
precise dating, because it's an ideal platform for doing so. A Microsoft
DLL is not necessarily designed to meet needs its coders never had in mind,
alas.
Dang, if they'd only gone to second precision. I'd be content with that, I
swear! ;-)
--
Scott|||You are fabricating an example to suit your definitions while makeing no
effort to distingush between instants and intervals. For someone who
recommends Snodgrass' work, you should really read chapters 3 and 11 of his
book.
--
Anith|||On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
>Basics: Time comes in durations, so evetns have a start and stop time.
Hi Joe,
Really?
In the Usenet headers of your message is this line:
>NNTP-Posting-Date: Tue, 30 Aug 2005 20:28:41 +0000 (UTC)
This denotes the time you decided to hit the "send" button (or whatever
it's called in your software) and publish your message to the Usenet.
Please tell me the start and stop time of posting this message?
On my desk is a letter. The poststamp on the envelope is stamped by the
Dutch postal service. This stamp includes a date: "22 VIII 05". Please
don't tell me that this means that they started stamping it on midnight
and took a full 24 hours before the stamping was done.
Think about tracking when a web advertisement was served. The NNTP
protocol can't track how long I look at the ad. (IIRC, it's even
impossible to track if I have an ad blocker active). All web advertising
contracts are based on how often the ad is served. What is the start and
stop time of serving an ad?
How about police work? An officer is checking the streets, and at 11:47
AM he sees you driving through a red light. What's the start and stop
times of that? Or if you are caught speeding? Sure, you started speeding
before the officer caught you, and you might have continued after that,
but there's no way that the dept of Justice will ever find out - but
they do know the exact time that an officer of the law saw on his
equipment that you were driving 7.3 mph too fast.
Need I go on, or do you now have enough examples to know that in the
real world, time does NOT always have start and stsop time.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||AK opined thusly on Aug 30:
> That done, you need to match every beginning to its corresponding end.
> This is very simple using row_number() available in SQL 2005
> In earlier versions, you can either emulate row_number() using
> identity() column in a result set, or use a join condition like this:
> ...
> from beginnings b join ends e on b.time<e.time
> where not exists(select 1 from beginnings b1 where b.time< b1.time and
> b1.time<e.time)
> and not exists(select 1 from endings e1 where b.time< e1.time and
> e1.time<e.time)
OK, here's the result in all its gory(sic). Still playing. It's interesting
to vary @.interval and see the consequences. Now I have to figure out how to
justify any particular value for that. Geeez . . .
| CREATE FUNCTION ie_begin(@.interval datetime)
| RETURNS TABLE
| AS
|
| Return
| (
| select i0.Username, i0.vDate
| from ieHist i0 where not exists
| (
| select vDate from ieHist where vDate > i0.vDate - @.interval and vDate < i0.vDate and i0.username = username
| )
| or exists
| (
| select vDate from ieHist where vDate < i0.vDate - @.interval and vDate > i0.vDate and i0.username = username
| )
| group by i0.username, i0.vDate
| )
| go
| CREATE FUNCTION ie_end(@.interval datetime)
| RETURNS TABLE
| AS
|
| Return
| (
| select i0.Username, i0.vDate
| from ieHist i0 where not exists
| (
| select vDate from ieHist where vDate < i0.vDate + @.interval and vDate > i0.vDate and i0.username = username
| )
| or exists
| (
| select vDate from ieHist where vDate > i0.vDate + @.interval and vDate < i0.vDate and i0.username = username
| )
| group by i0.username, i0.vDate
| )
| go
And here's my sandbox:
| declare @.interval as datetime
| declare @.beginnings table (username varchar(30), vtime datetime)
| declare @.ends table (username varchar(30), vtime datetime)
| set @.interval = '00:10'
| insert into @.beginnings (username, vtime) select * from ie_begin(@.interval)
| insert into @.ends (username, vtime) select * from ie_end(@.interval)
| select b.username, b.vtime, e.vtime, datediff(minute, b.vtime, e.vtime) as duration
| from @.beginnings b
| join @.ends e
| on b.vtime < e.vtime and b.username = e.username
|Aaugh where
| not exists
| (
| select 1 from @.beginnings b1 where b.vtime < b1.vtime and b1.vtime < e.vtime
| )
| and
| not exists
| (
| select 1 from @.ends e1 where b.vtime < e1.vtime and e1.vtime < e.vtime
| )
| go
With 5 minutes for @.interval, this was typical:
User_one 8/30/2005 1:28 PM 8/30/2005 1:30 PM 2
User_one 8/30/2005 1:36 PM 8/30/2005 1:37 PM 1
User_two 8/26/2005 12:40 PM 8/26/2005 12:42 PM 2
User_two 8/29/2005 6:52 AM 8/29/2005 6:55 AM 3
User_two 8/29/2005 10:34 AM 8/29/2005 10:38 AM 4
User_three 8/30/2005 3:52 PM 8/30/2005 3:59 PM 7
User_three 8/30/2005 4:06 PM 8/30/2005 4:07 PM 1
User_four 8/25/2005 12:17 PM 8/25/2005 12:18 PM 1
User_four 8/25/2005 1:33 PM 8/25/2005 2:02 PM 29
User_four 8/25/2005 2:02 PM 8/25/2005 2:21 PM 19
User_four 8/25/2005 2:28 PM 8/25/2005 2:32 PM 4
User_four 8/25/2005 2:44 PM 8/25/2005 3:27 PM 43
User_four 8/25/2005 4:28 PM 8/25/2005 4:30 PM 2
User_four 8/26/2005 3:17 PM 8/26/2005 3:19 PM 2
User_four 8/30/2005 4:28 PM 8/30/2005 4:29 PM 1
There's a LOT of work to do yet on this. Not bad for starters though.
Thanks again for pulling the cord on this old lawn-mower.
--
Scott|||Scott Marquardt opined thusly on Aug 31:
> OK, here's the result [...]
>| on b.vtime < e.vtime and b.username = e.username
>|Aaugh where
>| not exists
Pardon that. That was supposed to go into an instant message, not this
post. ;-)
--
Scott|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Need I go on, or do you now have enough examples to know that in the
> real world, time does NOT always have start and stsop time.
When I read your post, it was quite clear that there was something
fundamentally wrong with it, but I could not just put my finger on it.
Until I came to this last paragraph. You are seriously trying to refer
to real world in an argument with Joe Celko?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> The poststamp on the envelope is stamped by the Dutch postal service. This stamp includes a date: "22 VIII 05". Please
don't tell me that this means that they started stamping it on midnight
and took a full 24 hours before the stamping was done. <<
Do you really use that format?? I thought that Roman Numeral dates
went out with the NATO Standards under De Gaul. My age is showing.
For legal purposes in the US, that postmark would be the duration
('2005-08-22 00:00:00' to '2005-08-22 23:59:59.9999..)
>> How about police work? An officer is checking the streets, and at 11:47 AM he sees you driving through a red light. What's the start and stop times of that? <<
That deals with rounding errors and precision. The way I drive, it
means that the cop did not have a watch that goes to microseconds :)
The conceptual model is that it took some time for me to go thru the
intersection at 100 MPH.
I am starting to like the MySQL convention of 'yyyy-mm-00' for a whole
month range and ''yyy-00-00' for a whole year range, but I have trouble
with '0000-00-00' for eternity.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1125513734.900268.311850@.g49g2000cwa.googlegr oups.com...
> >> you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants.<<
> Do not confuse the recording of the data with the data model.
No one is.
> Think
> about a sign-in and sign-out sheet or timeclock. Each line is "half a
> fact"; the whole fact is the duration spent on the job. In this case,
> the user logs onto a site, stays there for x-minutes. He is not there
> for a Chronon (that is the term for a point in time in temporal
> databases).
What you're missing is most website log information is stateless. The
concept of a "duration" doesn't necessarily exist with webpages.
I.e you go to www.google.com and get a page.
Google records you requested a page. They have no idea how long you look at
it. You could get up, go have lunch, go for a walk, etc.
Shut down your computer, go to a different site, etc.
Celko, I suggest you go look at the logs of a webserver sometime. A
webpage is recorded as an instant in time.
Yes, one can try to model a visitors travel through a site, but one is not
necessarily modelling reality. They may pull up a page, go away for 5
minutes, and hit a link.
From that you can derive a "duration" they were on that page, but not
necessarily.
As I said, they could close their browser. You record no duration.
They could click a link to another site, nothing gets recorded in your logs.
Again, no duration.
They could simply type in a different URL, nothing gets recorded in your
logs. Again, no duration.
Or, they could go out for lunch, and come back and hit another page on your
site. But does that really mean that they spent a duration of an hour on
your site? Not raelly.
> So his table MIGHT look like this:
> CREATE TABLE Browsing
> (user_id VARCHAR(30) NOT NULL,
> website VARCHAR(255) NOT NULL,
> login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> logout_time TIMESTAMP, -- null means still there
> CHECK (login_time <= logout_time), -- less than one minute problem
> PRIMARY KEY (user_id, website, login_time));
> The real problem in this situaiton is having to round to the minute.
> We can force a convention on the stopping time to keep it away from the
> starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
> to 'yyyy-mm-ddThh:mm:59.997').
>
> Ever read the paradoxes of Zeno? He went thru what happens when you
> believe in Chronons. He lived in a time when Greek math was "integer
> only" and without a continuum.|||On Wed, 31 Aug 2005 16:49:20 -0500, Scott Marquardt wrote:
> Scott Marquardt opined thusly on Aug 31:
>> OK, here's the result [...]
>>| on b.vtime < e.vtime and b.username = e.username
>>|Aaugh where
>>| not exists
> Pardon that. That was supposed to go into an instant message, not this
> post. ;-)
But it's so appropriate! Reminiscent of the required "PLEASE" statements
in INTERCAL (Language Without A Good Acronym).|||I did not say that very well. The model is that the traffic light was
modeled by Lights (color, start_time, end_time). and that I was in the
intersection with ('red', 2005-09-01 12:00:00', '2005-09-01
12:20:00'). I need to talk to the city about a traffic light with a 20
minute duration.|||Joe,
That's a very valid observation about the seperation of the data model
from the data recorded; there are two logistical problems raised by
this, however:
1. The tools that collect web traffic information (typically firewall
syslog) are at best a proxy measure for web usage; what they really
capture is connection information. The firewall doesn't know what
happens to a packet when it passes by; all it can say is that at this
instant (Chronon; neat term), a packet passed through the firewall from
one computer to the next.
Many people use this information to try and gather web usage, but it's
an imperfect model. There is no accurate way (as Scott said earlier)
to indicate how much time a person actually spent interacting with a
web site. All that can be said for certain is that a packet passed
from a machine that's associated with that user to the Internet at x
time.
2. The other logistical problem that is raised is the issue of
multitasking. Right now, I have 6 browser tabs open (in Firefox on a
multi-monitor system); I switch back and forth looking at different
information. How much time am I spending on a website? It can't be
measured looking at syslog data because every time I interact with a
different web page, a connection event gets recorded. Any report that I
run trying to decipher my web behavior would be nearly impossible to
interpret (e.g., Stu went to CNN then to Google Groups back to CNN, to
email, back to CNN, and then to Google Groups. All in the span of a
few seconds).
Stu|||"Time is what keeps everythign from happening at once!" -- George
Karlin.
Yes, parallelism is a bitch. I'd handle the multitasking by modeling
the session/connection rather than the user, then attach the user to
each of those. But the truth is that things were in durations -- maybe
short ones (Aunt Mabel's photos) or long ones (disgusting_porno.com)
and we have the problem of only being able to catch "half a fact".
I did a schema for a company that does a electronic timeclock system
that recorded nothing but the id of a fob and a UTC time. The fobs are
color coded for each job and you touch them to the mil spec timeclock
-- it looks like a pad lock that can take a bullet at point blank
range. If you think about it, you can do a lot with a minimal amount
of data.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1125612157.839762.92160@.o13g2000cwo.googlegro ups.com...
> "Time is what keeps everythign from happening at once!" -- George
> Karlin.
> Yes, parallelism is a bitch. I'd handle the multitasking by modeling
> the session/connection rather than the user, then attach the user to
> each of those. But the truth is that things were in durations -- maybe
> short ones (Aunt Mabel's photos) or long ones (disgusting_porno.com)
> and we have the problem of only being able to catch "half a fact".
Joe, again, I will repeat the point that many websits/pages don't have any
concept of session/connection. It's stateless.
So yes, exactly the problem is being able to catch "half a fact". The
"fact" in this case is the user requested the page at time A. Nothing more,
nothing less.
> I did a schema for a company that does a electronic timeclock system
> that recorded nothing but the id of a fob and a UTC time. The fobs are
> color coded for each job and you touch them to the mil spec timeclock
> -- it looks like a pad lock that can take a bullet at point blank
> range. If you think about it, you can do a lot with a minimal amount
> of data.|||On 31 Aug 2005 15:40:46 -0700, --CELKO-- wrote:
>>> The poststamp on the envelope is stamped by the Dutch postal service. This stamp includes a date: "22 VIII 05". Please
>don't tell me that this means that they started stamping it on midnight
>and took a full 24 hours before the stamping was done. <<
>Do you really use that format?? I thought that Roman Numeral dates
>went out with the NATO Standards under De Gaul. My age is showing.
Hi Joe,
I don't. But the Dutch postal service does.
>For legal purposes in the US, that postmark would be the duration
>('2005-08-22 00:00:00' to '2005-08-22 23:59:59.9999..)
Ah. So you really believe that the letter was in the machine that stamps
the stamps for a full 24 hours?
>>> How about police work? An officer is checking the streets, and at 11:47 AM he sees you driving through a red light. What's the start and stop times of that? <<
>That deals with rounding errors and precision. The way I drive, it
>means that the cop did not have a watch that goes to microseconds :)
>The conceptual model is that it took some time for me to go thru the
>intersection at 100 MPH.
But the warrant (is that the correct term? Bablefish thinks it is) does
not show how long it took you to go through an intersection. It shows at
what instant in the time continuum the officer measured your speed, the
result of that measurement, and the fine you'll have to pay.
>I am starting to like the MySQL convention of 'yyyy-mm-00' for a whole
>month range and ''yyy-00-00' for a whole year range,
Really?
What will MySQL return if you execute
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
WHERE TheDay < '2005-08-00'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> What will MySQL return if you execute
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
WHERE TheDay < '2005-08-00' <<
I don't know; I have not played with MySQL. However, Snodgrass defined
a set of relationships between internvals -- overlaps, precedes,
during, etc. to replace the usual scalar comparisons.|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> What will MySQL return if you execute
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
> WHERE TheDay < '2005-08-00'
It produces a syntax error. It appears that it does not support
naming the columns for the derived table in that fashion. But
this query:
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X
WHERE TheDay < '2005-08-00'
Returns:
+-------+
| TheDay |
+-------+
| 2005-07-20 00:00:00 |
+-------+
1 row in set (0.00 sec)
I also tried:
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-08-01' AS datetime)) AS X
WHERE TheDay < CAST('2005-08-00' AS datetime)
With the same result, whereas changing 2005-08-01 to 2005-07-31 added
that date to the result set.
After all, the concept is not that difficult. 2005-08-00 is just a point
between 2007-07-31 and 2007-08-01. The internal representation is probably
not a plain numeric value as in SQL Server.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> After all, the concept is not that difficult. 2005-08-00 is just a point
> between 2007-07-31 and 2007-08-01. The internal representation is probably
> not a plain numeric value as in SQL Server.
Some more findings. What would you expect this to return:
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-08-01' AS datetime)) AS X
WHERE TheDay = CAST('2005-08-00' AS datetime);
From what Joe said I would expect two rows, but I got no rows back.
And, yes, this works:
mysql> CREATE TABLE xf(a datetime NOT NULL)
-> ;
Query OK, 0 rows affected (2.24 sec)
mysql> INSERT xf (a) VALUES('2008-05-00')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from xf
-> ;
+-------+
| a |
+-------+
| 2008-05-00 00:00:00 |
+-------+
1 row in set (0.01 sec)
In fact, this was accepted too:
INSERT xf (a) VALUES('2008-05-420')
This "date" is then presented as 0000-00-00.
Explicit conversion to integer does not seem to be permitted, but implicit
appears to happen here:
mysql> select 1 + a from xf;
+------+
| 1 + a |
+------+
| 20080500000001 |
| 1 |
+------+
Funny creature, MySQL!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sat, 3 Sep 2005 09:46:22 +0000 (UTC), Erland Sommarskog wrote:
>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> What will MySQL return if you execute
>>
>> SELECT *
>> FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
>> SELECT CAST('2005-08-10' AS datetime) UNION ALL
>> SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
>> WHERE TheDay < '2005-08-00'
>It produces a syntax error. It appears that it does not support
>naming the columns for the derived table in that fashion. But
>this query:
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X
> WHERE TheDay < '2005-08-00'
>Returns:
> +-------+
> | TheDay |
> +-------+
> | 2005-07-20 00:00:00 |
> +-------+
> 1 row in set (0.00 sec)
>I also tried:
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-08-01' AS datetime)) AS X
> WHERE TheDay < CAST('2005-08-00' AS datetime)
>With the same result, whereas changing 2005-08-01 to 2005-07-31 added
>that date to the result set.
>After all, the concept is not that difficult. 2005-08-00 is just a point
>between 2007-07-31 and 2007-08-01. The internal representation is probably
>not a plain numeric value as in SQL Server.
Hi Erland,
Thanks for giving this a try. The enxt step (I actually intended this to
be the first step, but typo-ed when writing the message) would be to
test:
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X
WHERE TheDay > '2005-08-00'
Based on the concvept of using yyyy-mm-00 as a range, I'd expect one
row. But based on your other findings, I'm afraid that you will actually
get two rows.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 2 Sep 2005 18:50:50 -0700, --CELKO-- wrote:
>>> What will MySQL return if you execute
>SELECT *
>FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
>WHERE TheDay < '2005-08-00' <<
>I don't know; I have not played with MySQL. However, Snodgrass defined
>a set of relationships between internvals -- overlaps, precedes,
>during, etc. to replace the usual scalar comparisons.
Hi Joe,
If you haven't tested how this non-ANSI, non-portable, super-proprietary
MySQL daterange representation behaves in actual queries, then what
exactly do you base this statement on:
>I am starting to like the MySQL convention of 'yyyy-mm-00' for a whole
>month range and ''yyy-00-00' for a whole year range,
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 3 Sep 2005 18:10:52 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>Funny creature, MySQL!
Hi Erland,
You can say that again.
But it's also quite worrying to see a guru as Joe Celko actually endorse
the way MySQL handles dates...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Thanks for giving this a try. The enxt step (I actually intended this to
> be the first step, but typo-ed when writing the message) would be to
> test:
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X
> WHERE TheDay > '2005-08-00'
> Based on the concvept of using yyyy-mm-00 as a range, I'd expect one
> row. But based on your other findings, I'm afraid that you will actually
> get two rows.
Confirmed.
> But it's also quite worrying to see a guru as Joe Celko actually endorse
> the way MySQL handles dates...
Joe will have to come up with in his own excuses, but we know that he
is often wrong about MS SQL Server, so why should he know MySQL any
better? :-)
Anyway, if you want more musings about MySQL, have a look at
http://sql-info.de/mysql/gotchas.html. Section 1.14 relates to
this thread. (In fairness, there are enough weirdnesses in SQL Server
to warrant a similar list for SQL Server.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hey, all I said was that I liked the syntax as a shorthand for an
interval. I do not like MySQL and consider it to be a file system and
not much of a DB yet.
ly.
math was "integer