List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 24 2006 10:56pm
Subject:Re: Fun with Dates and Incentives.
View as plain text  
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 J
>
>  
>
> -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 <http://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
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006
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