Member Avatar for muppet

I have a machine logging production info to a database whenever certain conditions change state, so pretty much randomly throughout the day. The plant runs 24/7 so data is continuously flowing into it with about 500 entries in a 24 hour cycle.

The shifts are strictly time based and are as follows,
07:20-15:20 (day shift)
15:20-11:20 (swing shift)
11:20-07:20 (night shift)

I need to select all of the last entries for each shift, so the results should look something like this:

id  |  timestamp          |  bags_packed
34  | 2010-10-02 15:19:24 |  21320
73  | 2010-10-02 11:19:12 |  18432
93  | 2010-10-03 07:19:42 |  15035
163 | 2010-10-03 15:18:56 |  23937
194 | 2010-10-03 11:19:53 |  19304

(theres a whole lot more info than just bags_packed, but I'm keeping it simple)

So I am left with the final results for every shift and I can use this information to generate shift report trends over the year etc.

Edited by muppet because: n/a
  • 2 Contributors
  • 3 Replies
  • 82 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

How does a single entry look like? How do you compute bags_packed? Is it a sum or a count or a field value?

Member Avatar for muppet

I only used bags_packed as an example, there is alot of data being added to the database with a new line added about every 30 seconds. So there are thousands of entries in the table, for this particular problem I just need to see the last entry for each shift.

Member Avatar for smantscheff 
drop table sample;
create table sample
(id integer,
timestamp datetime,
bags_packed integer);

insert into sample values
('33  ',' 2010-10-02 15:19:23 ','  1'),
('34  ',' 2010-10-02 15:19:24 ','  21320'),
('72  ',' 2010-10-02 11:19:11 ','  2'),
('73  ',' 2010-10-02 11:19:12 ','  18432'),
('92  ',' 2010-10-03 07:19:41 ','  3'),
('93  ',' 2010-10-03 07:19:42 ','  15035'),
('999 ',' 2010-10-03 15:18:55 ','  4'),
('163 ',' 2010-10-03 15:18:56 ','  23937'),
('194 ',' 2010-10-03 11:19:53 ','  19304');

select * from sample where timestamp in (
select max(timestamp) from sample 
group by floor((unix_timestamp(timestamp)+40*60)/(8*3600))
);

There is still an error in it, but this is the direction.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.