Brian, Just my 2 cents :)
I always try to use an epoch time stamp for anything time related. its easier to compare times and all the functions are built into mysql to convert to/from a unix epoch timestamp select unix_timestamp(NOW()); +-----------------------+ | unix_timestamp(NOW()) | +-----------------------+ | 1148492137 | select from_unixtime(1148492137); +---------------------------+ | from_unixtime(1148492137) | +---------------------------+ | 2006-05-24 13:35:37 | Need the number of minutes between 2 epoch timestamp? subtract and divide by 60. select (unix_timestamp(NOW()) - 1148492137)/60; +-----------------------------------------+ | (unix_timestamp(NOW()) - 1148492137)/60 | +-----------------------------------------+ | 2.32 | George Law > -----Original Message----- > From: Brian Menke [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 12:51 PM > To: mysql@lists.mysql.com > Subject: Fun with Dates and Incentives. > > 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 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
