List:General Discussion« Previous MessageNext Message »
From:George Law Date:May 24 2006 5:38pm
Subject:RE: Fun with Dates and Incentives.
View as plain text  
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:Brian@stripped]
> Sent: Wednesday, May 24, 2006 12:51 PM
> To: mysql@stripped
> 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
> 
> 

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