List:General Discussion« Previous MessageNext Message »
From:Brian Menke Date:May 25 2006 6:19pm
Subject:RE: Fun with Dates and Incentives.
View as plain text  
Thanks Sheeri, but now I'm a bit more confused. Does that mean that the
scenario that I listed below does not work even if I use UTC_TIMESAMP?
That's what I still don't get, if someone on the east coast submits their
test, and the web code on my server (which is on the west coast) is what
actually performs the INSERT using UTC_TIMESTAMP, then it seems like this
still doesn't work. This is driving me crazy :-) Any ideas? Is the bottom
line that I have to have a specific time zone for each student in my
database so I can do comparisons that way? 

I'm thinking about webex and how they send emails that automatically adjusts
for time zones. I think at some point, I had to tell them what time zone I
was in. I see that a lot on the web. Seems like a pain, but I don't see any
way around it.

-Brian

-----Original Message-----
From: sheeri kritzer [mailto:awfief@stripped] 
Sent: Thursday, May 25, 2006 9:13 AM
To: Brian Menke
Cc: peter.brawley@stripped; mysql@stripped
Subject: Re: Fun with Dates and Incentives.

Brian,

MySQL's timestamp function is based on the *server*'s timestamp, not
the client's.

-Sheeri

On 5/24/06, Brian Menke <brian@stripped> wrote:
> Wow, if I read that right, it means someone on the East coast submits
> answers to a test, it's somehow adjusted to be the same as someone who
> submits answers to a test from the west coast 3 hours later (time zone
> wise)? I can't possibly imagine how that works, but if it does that solves
> huge problems for me and I seriously owe you! I'm gonna do some more
> research so I understand how this works.
>
>
>
> THANKS!
>
>
>
> -Brian
>
>
>
>   _____
>
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: Wednesday, May 24, 2006 3:56 PM
> To: Brian Menke
> Cc: mysql@stripped
> Subject: Re: Fun with Dates and Incentives.
>
>
>
> Brian,
>
> CURRENT_TIMESTAMP gives you time in your server's timezone. UTC_TIMESTAMP
> gives GM (universal) time, so dispenses with all timezone adjustments.
> Trouble is, you cannot use it as a defalt. You have to pass it as an
INSERT
> value.
>
> PB
>
> -----
>
> Brian Menke wrote:
>
> Peter, thanks for the detailed info. I will figure out how to get rid of
the
> UNIQUE key. Somehow that got added. Thanks for the catch. As far as INT
for
> student id goes, I'm using email because it will be unique, and offers an
> easy way to track a user through the app I'm building (user name,
password,
> session id's etc.) but I do get what you are saying. Thanks for the
> UTC_TIMESTAMP suggestion. Although, since I haven't had a lot of
experience,
> I don't really understand why it is better than CURRENT_TIMESTAMP. But.
> that's why I asked for advice :-)
>
>
>
> -Brian
>
>
>
>   _____
>
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: Wednesday, May 24, 2006 1:31 PM
> To: Brian Menke
> Cc: mysql@stripped
> Subject: Re: Fun with Dates and Incentives.
>
>
>
> Brian
>
> Re your schema,
>   --it's redundant to define PRIMARY and UNIQUE keys on the same column,
>   --why not an INT student id?
>   --what if two (eg married) students share an email account?
>   --comparing datetimes across multiple time zones will be simpler if you
>     set completed_modules.time=UTC_TIMESTAMP in each new row of that
table.
>
> That would give ...
>
> CREATE TABLE students (
>   id INT NOT NULL,        -- auto_increment [simplest] or assigned by
> school?
>   email varchar(64) NOT NULL,
>   fname varchar(32) NOT NULL,
>   lname varchar(32) NOT NULL,
>   role char(2) NOT NULL default '5',
>   password varchar(8) NOT NULL,
>   phone varchar(24) default NULL,
>   reg_date date default NULL,
>   PRIMARY KEY (id),
>   KEY email (email)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> CREATE TABLE completed_modules (
>   id INT NOT NULL,
>   module_id char(2) NOT NULL default '',
>   score INT NOT NULL default 0,
>   time timestamp NOT NULL default CURRENT_TIMESTAMP
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> To find the first 10 scores of 100 on a particular module, just ...
>
> SELECT
>   CONCAT(s.lname,', ',s.fname) AS Name,
>   c.time,
>   c.score
> FROM students s
> INNER JOIN completed_modules c USING (id)
> WHERE c.module_id = 1 AND c.score = 100
> ORDER BY c.time ASC
> LIMIT 10;
>
> PB
>
>
>
>
>
> I'm hoping for some general advice on an approach for the following
> scenario:
>
>
>
> I have a customer who wants to put an incentive program in place for
> students taking learning modules and then completing tests. The concept is
> simple. Award the first 10 people who complete a test with a score of
> 100%... that type of thing. Students are allowed to take test more than
> once. Track each time the student takes the test and show the latest score
> ect. You get the idea. I have the database tables and relationships
already
> all set up for the tests, but it's the tracking of the dates and times
that
> I don't have and it got me thinking.
>
>
>
> I need to track down to the day/hour/minute level. Okay, that should be
easy
> (I think). I'm going to need to do a lot of date/time calculations. Would
it
> be best just to have a default of CURRENT_TIMESTAMP set for a TIMESTAMP
> field? Or, is their something else I should be using? I have limited
> experience having to munge and crunch date/time info and I want to make
sure
> I have the flexibility to do what I need in the future.
>
>
>
> The next gotcha I thought up is what about different time zones. Obviously
> without this consideration, people on the East coast would have an unfair
3
> hour advantage over people on the west coast. I guess I can have a time
zone
> field in my student table so I could derive the time difference. Any
> suggestions on a good time zone approach?
>
>
>
> Here are my two tables as they stand now. I'm wondering if these are set
up
> in a way to allow me to do all this date time crunching I'm going to need
to
> do in the future? Any suggestions are greatly appreciated :-)
>
>
>
>
>
> CREATE TABLE `students` (
>
>   `store_id` varchar(6) NOT NULL,
>
>   `email` varchar(64) NOT NULL,
>
>   `fname` varchar(32) NOT NULL,
>
>   `lname` varchar(32) NOT NULL,
>
>   `role` char(2) NOT NULL default '5',
>
>   `password` varchar(8) NOT NULL,
>
>   `phone` varchar(24) default NULL,
>
>   `reg_date` date default NULL,
>
>   PRIMARY KEY  (`email`),
>
>   UNIQUE KEY `email` (`email`)
>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
>
>
> CREATE TABLE `completed_modules` (
>
>   `module_id` char(2) NOT NULL default '',
>
>   `email` varchar(64) NOT NULL,
>
>   `score` int(2) NOT NULL default '0',
>
>   `time` timestamp NOT NULL default CURRENT_TIMESTAMP
>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
>
>
> Brian Menke
>
> Visual Matter, Inc
>
> 1445 Foxworthy Ave., Suite 50-215
>
> San Jose, CA 95118
>
> 408 375 9969
>
>
>
> San Jose ~ Los Angeles
> www.visualmatter.com
>
>
>
>
>
>
>
>
>
>
>
>
>
>   _____
>
>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006
>
>
>
>
>
>
>
>   _____
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006
>
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Fun with Dates and Incentives.Brian Menke24 May
  • Re: Fun with Dates and Incentives.Jason Dimberg24 May
  • Re: Fun with Dates and Incentives.Peter Brawley24 May
    • RE: Fun with Dates and Incentives.Brian Menke24 May
      • Re: Fun with Dates and Incentives.Peter Brawley25 May
        • RE: Fun with Dates and Incentives.Brian Menke25 May
          • Re: Fun with Dates and Incentives.sheeri kritzer25 May
            • RE: Fun with Dates and Incentives.Brian Menke25 May
              • Re: Fun with Dates and Incentives.Peter Brawley25 May
              • Re: Fun with Dates and Incentives.sheeri kritzer26 May
      • Re: Fun with Dates and Incentives.Jeff Shapiro26 May
      • Re: Fun with Dates and Incentives.Jeff Shapiro26 May
  • Re: Fun with Dates and Incentives.Peter Brawley25 May
RE: Fun with Dates and Incentives.George Law24 May