Posted by --CELKO-- on 08/14/07 16:37

Please post DDL, so that people do not have to guess what the keys,
constraints, declarative referential integrity, data types, etc. In
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

CREATE TABLE UserLogs
(user_id INTEGER NOT NULL,
login_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
CHECK (login_date
= CAST(FLOOR(CAST(login_date AS FLOAT)) AS DATETIME)),
PRIMARY KEY (user_id, login_date),
etc.);

There are some other tricks for clipping off the time, but you must
put it in the DDL to have data integrity. Here is another one:
DATEADD(DAY, DATEDIFF(DAY, 0, login_date), 0)

Just don't convert to strings and then cast back to DATETIME --
overhead is a bit much. Remember to use the Standard CURRENT_TIMESTAMP
and not the proprietary getdate() from the old UNIX days of Sybase.

>> 1. To find the data for today, <<

CREATE VIEW CurrentUserLogs (user_id, login_date, ..)
AS
SELECT user_id, login_date, ..
FROM UserLogs
WHERE login_date = CURRENT_TIMESTAMP;

This VIEW will always be correct when you invoke it.

Another VIEW might help, if you need this two-day window for other
things. Why would you want to constantly be updating this table to
materialize this two-day window? You can clean it out later with a
DELETE FROM Userlogs WHERE login_date < (CURRENT_TIMESTAMP,
DATEADD(DAY, CURRENT_TIMESTAMP, -10); so that you have some history
just in case.

CREATE VIEW RecentUserLogs (user_id, login_date, ..)
AS
SELECT user_id, login_date, ..
FROM UserLogs
WHERE login_date IN (CURRENT_TIMESTAMP, DATEADD(DAY,
CURRENT_TIMESTAMP, -1);

>> 2. What if the table was updated twice or more on the same day from the same data, using UPDATE/INSERT SQL seems to not overwrite the columns with today's date, it's as if SQL is secretly inserting the time by itself <<

And that is why we have a proper key and trim to the date in the DDL.
You cannot violate that business rule now that it is a constraint.

>> 3. I would like to compare today's rows with yesterday and find rows that were there yesterday but not present today. Go with Roy's answer:

SELECT user_id, etc, ..
FROM UserLogs -- or use RecentUserLogs instead
GROUP BY user_id
HAVING MAX(login_date) = DATEADD(DAY, CURRENT_TIMESTAMP, -1);

[Back to original message]