Re: Fun with Dates and Incentives.
On Wednesday 24 May 2006 15:18, Brian Menke scribble on about: 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 Umm just a question here: What happens if the person changes his or her email address? That is if you are allowing them to change it. If you are using it as the table relationship key, it may break your relationships if it changes in the parent table.. You'll have to ensure that your parent and child table keys stay in sync. This could be a headache waiting to happen. Normally, I place an autoincrement column in the parent table and use it as the relationship key for all child tables. This way I know that the value will never change and it's not part of data important to the end user or the application. This is basically what Peter suggested. jeff -- Counting in octal is just like counting in decimal--if you don't use your thumbs. -- Tom Lehrer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fun with Dates and Incentives.
On Wednesday 24 May 2006 15:18, Brian Menke scribble on about: 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 Umm just a question here: What happens if the person changes his or her email address? That is if you are allowing them to change it. If you are using it as the table relationship key, it may break your relationships if it changes in the parent table.. You'll have to ensure that your parent and child table keys stay in sync. This could be a headache waiting to happen. Normally, I place an autoincrement column in the parent table and use it as the relationship key for all child tables. This way I know that the value will never change and it's not part of data important to the end user or the application. This is basically what Peter suggested. jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fun with Dates and Incentives.
Brian, The scenario you list, that a person on the east coast and a person on the west coast will submit tests in 3 hours apart yet show up in the db as having been done at the same time, will not happen. No matter what timestamp you use on the server, UTC or otherwise, it's going to insert the server time into the database. If your server is in PST and a person in EST submits a test at 3 pm, it will look in the database as if it was submitted at 12 noon PST. or 8 pm GMT. Whatever. Time is time, and no matter what format you use, the server records the time it currently is. -Sheeri On 5/25/06, Brian Menke [EMAIL PROTECTED] wrote: 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:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 9:13 AM To: Brian Menke Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 3:56 PM To: Brian Menke Cc: mysql@lists.mysql.com 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 3:56 PM To: Brian Menke Cc: mysql@lists.mysql.com 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
RE: Fun with Dates and Incentives.
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:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 9:13 AM To: Brian Menke Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 3:56 PM To: Brian Menke Cc: mysql@lists.mysql.com 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
Re: Fun with Dates and Incentives.
Brian, It depends on how you determine submission date. If you take it from the header in the email client which receives the entry, it depends on how your email client determines the date. You could bypass all this variability by always inserting entries in the order in which they arrive. But that's lossy To preserve the email handler's version of the time, but still avoid the sort of date arithmetic complications you originally asked about, translate entry email-receive time into UTC time, which as you see is simple arithmetic, and do your serverside date arithmetic in UTC time. PB - 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:[EMAIL PROTECTED]] Sent: Thursday, May 25, 2006 9:13 AM To: Brian Menke Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]] Sent: Wednesday, May 24, 2006 3:56 PM To: Brian Menke Cc: mysql@lists.mysql.com 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:[EMAIL PROTECTED]] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
RE: Fun with Dates and Incentives.
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]
Re: Fun with Dates and Incentives.
I think doing a sort by date with limit 10 should get you the first ten. I believe the now() function uses the server time, so no need to do date/time calcs really. Good luck, Jason Brian Menke wrote: 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]
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 outgoing 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/[EMAIL PROTECTED]
RE: Fun with Dates and Incentives.
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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
Re: Fun with Dates and Incentives.
Brian, Somehow the server ate my response... 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 -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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, Im using email because it will be unique, and offers an easy way to track a user through the app Im building (user name, password, session ids etc.) but I do get what you are saying. Thanks for the UTC_TIMESTAMP suggestion. Although, since I havent had a lot of experience, I dont really understand why it is better than CURRENT_TIMESTAMP. But thats why I asked for advice J -Brian From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
RE: Fun with Dates and Incentives.
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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 3:56 PM To: Brian Menke Cc: mysql@lists.mysql.com 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:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com 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
