| Data base making unauthorized changes
What error am I making here |
Baruch Menachem
msg:4416039 | 7:33 pm on Feb 9, 2012 (gmt 0) |
I want to make a little time card application. This is the start of the preliminary testing, which is already going a bit wrong
create table timesheet(
record_no int(12) auto_increment primary key,
emp_no int(6),
punch_in timestamp,
punch_out timestamp)
insert into timesheet(empno, punch_in) values (86,now());
mysql> select * from timesheet;
+-----------+--------+---------------------+---------------------+
| record_no | emp_no | punch_in | punch_out |
+-----------+--------+---------------------+---------------------+
| 1 | 86 | 2012-02-09 10:15:25 | 0000-00-00 00:00:00 |
| 2 | 99 | 2012-02-09 10:15:48 | 0000-00-00 00:00:00 |
+-----------+--------+---------------------+---------------------+
So far so good. now the next step, which creates problems
insert into timesheet(emp_no,punch_in) values (13,now());
mysql> update timesheet set punch_out=now() where emp_no=86;
select * from timesheet;
+-----------+--------+---------------------+---------------------+
| record_no | emp_no | punch_in | punch_out |
+-----------+--------+---------------------+---------------------+
| 1 | 86 | 2012-02-09 10:43:17 | 2012-02-09 10:43:17 |
| 2 | 99 | 2012-02-09 10:15:48 | 0000-00-00 00:00:00 |
| 3 | 13 | 2012-02-09 10:19:21 | 0000-00-00 00:00:00 |
+-----------+--------+---------------------+---------------------+
Why does the call to update punch_out also update the value to punch in?
|
Dijkgraaf
msg:4416118 | 1:27 am on Feb 10, 2012 (gmt 0) |
After creating the table with your script and then exporting the definition it is as follows.
CREATE TABLE IF NOT EXISTS `timesheet` (
`record_no` int(12) NOT NULL auto_increment,
`emp_no` int(6) default NULL,
`punch_in` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`punch_out` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`record_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
What you want is
CREATE TABLE IF NOT EXISTS `timesheet` (
`record_no` int(12) NOT NULL auto_increment,
`emp_no` int(6) default NULL,
`punch_in` timestamp NOT NULL default CURRENT_TIMESTAMP,
`punch_out` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`record_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
See the difference?
I looks like if you don't set the default that for the first time stamp it not only sets the default, it also sets on update CURRENT_TIMESTAMP as well.
|
Baruch Menachem
msg:4416299 | 4:07 pm on Feb 10, 2012 (gmt 0) |
Thanks.
Now for the next problem.....
The machine does odd math with the time stamps. Is this just not a good tool for this purpose?
update timesheet set worked_hours=
sec_to_time(
time_to_sec(punch_out)-
time_to_sec(punch_in)
)where record_no in (1,2)
mysql> select * from timesheet;
+-----------+--------+---------------------+---------------------+--------------+
| record_no | emp_no | punch_in | punch_out | worked_hours |
+-----------+--------+---------------------+---------------------+--------------+
| 1 | 86 | 2012-02-09 19:53:52 | 2012-02-09 20:18:43 | 00:24:51 |
| 2 | 99 | 2012-02-09 19:54:04 | 2012-02-10 07:30:49 | -12:23:15 |
| 3 | 13 | 2012-02-09 19:54:26 | 0000-00-00 00:00:00 | NULL |
| 4 | 76 | 2012-02-09 19:57:50 | 0000-00-00 00:00:00 | NULL |
+-----------+--------+---------------------+---------------------+--------------+
Thanks again
|
rocknbil
msg:4416322 | 4:54 pm on Feb 10, 2012 (gmt 0) |
you'd probably want to do that using timediff() [dev.mysql.com] or some other mysql function. Reason being . . . what about the graveyard shift or late night redeye projects? Start and end might be on different days.
update timesheet set worked_hours=timediff(punch_out-punch_in) where record_no in (1,2)
you might add formatting to round it to the nearest second, but that's a good start.
|
|
|
