sql statements

[jameswilson]

does anyone know how to order a particular column descending

[zoneminder]

select a,b,c from x order by b desc (or 'asc' or 'a desc, b asc' etc).

Phil

[jameswilson]

thank phil

[jameswilson]

Code: Select all

SELECT E.Id, E.StartTime, E.EndTime, E.Frames, E.Length FROM zm.`Events` order by Id asc E  Where MonitorId = 2

I have the above statement and i am using query manager to test but it bombs saying i have an error in my query stratement

SELECT E.Id, E.StartTime, E.EndTime, E.Frames, E.Length FROM zm.`Events` E Where MonitorId = 2

works great but isnt in the order i want

James

[jameswilson]

oh and would it be possible to query the db to get only images with movement in them i was thinking having columns with first frame nuber to last frame number that way i can use these as my image name in the path to image.

Or would it be better to have an individual row for each frame with movement

What i am trying to achieve (and if all i need is different sql stements then i can have tonnes of different options) is a stream of alarms only from a particular monitor. I can already do this assuming the camera is in modect mode but as i use mocord i have to find the movement manually. If i could press a button that would execute a different sql query (as above) then all i would get is a stream of events but these events would all be ones in alarm.

Currently i query the number of frames for an event and for next

ie a = 1 to <number of frames>
when this ends it jumps to the next record and start foe for next loop again. NOw im assuming that the first frame would be say 208 and the last frame would be 228. So i would need the row to include start frame and end frame.

ie a = <start frame> to <end frame>
blah
next a

Is this possible or do i need better way?

James

[zoneminder]

Code: Select all

SELECT E.Id, E.StartTime, E.EndTime, E.Frames, E.Length FROM zm.`Events` order by Id asc E  Where MonitorId = 2

You have a typo in this. So you can either do

Code: Select all

SELECT E.Id, E.StartTime, E.EndTime, E.Frames, E.Length FROM zm.`Events` as E order by Id asc where MonitorId = 2;

or

Code: Select all

SELECT Id, StartTime, EndTime, Frames, Length FROM zm.`Events` order by Id asc where MonitorId = 2;

The 'as E' part in the first example just adds an alias so you can shortcut references to a table. But if you only have one table in a query then you don't need any kind of reference so you can use the more concise second one.

Phil

[zoneminder]

oh and would it be possible to query the db to get only images with movement in them i was thinking having columns with first frame nuber to last frame number that way i can use these as my image name in the path to image.

You can query the Frames table and look for frames with a Score > 0 if you only want frames with motion. Is that what you mean?

Currently i query the number of frames for an event and for next

ie a = 1 to <number of frames>
when this ends it jumps to the next record and start foe for next loop again. NOw im assuming that the first frame would be say 208 and the last frame would be 228. So i would need the row to include start frame and end frame.

ie a = <start frame> to <end frame>
blah
next a

Each frame record has a unique id but also an id within the event. So if an event has 29 frames then you will have frames with a FrameId of 1 to 29 even if their Id fields are 12654 to 12673 or whatever. So basically just query the the frames table for an event and order by FrameId to get all the frames in order. Put a 'where Score > 0' on the end if all you want are the frames with detected motion, bearing in mind that the frames before and after is motion is detected usually have interesting information in them also.

Phil

[jameswilson]

Phil thanks for the sql select staements but neither of them work

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Where MonitorId = 2' at line 1

when

Code: Select all

SELECT E.Id, E.StartTime, E.EndTime, E.Frames, E.Length FROM zm.`Events` as E order by Id asc where MonitorId = 2;

[jameswilson]

sorted it i needed a ; at the end of the query

Cheers Phil

James

[zoneminder]

That will teach me to post without checking. The order and where statements are in the wrong order so it should be

Code: Select all

SELECT E.Id, E.StartTime, E.EndTime, E.Frames, E.Length FROM zm.`Events` as E where MonitorId = 2 order by Id asc;

for example. I would be surprised if the ; at the end fixed it. What it might have done is trated the ; as a extra command. I also add the ; because I use the mysql command line utility but I don't think you need it in guis or managers.

Phil

[jameswilson]

I used the mysql query manager and found it can build these things for you drag and drop stylee. Anyway i got it sorted just applying the finishing touches to it now i is well impressed

It cycled through indivual frames of alarm so you get modect style playback from a mocord recorded monitor

GREAT!!

btw it appears that mocord mode doesnt insert the prealarm frames from the buffer is the right or am i not looking at it correctly. The reason i have spotted this is i normally use 2-3 fps and a frame skip of around 5
When movemnt is detected i get 2-3 fps but only from the point of alarm. Before hand its skipping as it should so im supposing those framed are dropeed as i cant see how you could insert them cos the order would be all out

James

[jameswilson]

phil you had a look at the above post?

And i cant seem to rasie a sql statement that is on sync

What im thinking is to have 4 monitors selectable via combo box. but to start with lets assume mon 1,2,3 and 4
If we have the sql statement get all events for a particular day and have a new row for each image (like motion)
id like the table to have 4 records for each linked event
ie
line 1 mon1 at 12:00:00
line 2 mon2 at 12:00:00 etc etc
line 5 mon1 at 12:00:01
line 6 mon2 at 12:00:01

i could then step through each line in the data set requesting images from apache based on the image number etc (like motion only)

im trying to think of another way but i think all the records need to be in one table to keep the images in sync when playing forward the reverse etc

James

[zoneminder]

If I understand correctly you just want to do something like

Code: Select all

select * from Events where StartTime > 2005-12-05 order by StartTime;

Is that what you were after? I think you are correct about the mocord pre-alarm frames as by the time an alarm occurs the previous frame may already have been inserted.

Phil

[jameswilson]

Phil yes i will have a play with the sql statemants and come back to you as i have a few issues to overcome first

Regarding the mocord thing, yes the prealarm frames would already be there as long as you have frameskip to 0

What i usually do is set the max fps to 2-4 and a skip of 5-10
that gives me useful footage on playback and 2-4 fps on motion, but i get skipped frames until the actual alarm. I suppose it would help the system then to have a 0 prealarm buffer when on mocord as if it aint doing anything with those frames there is no point keeping them in ram.

James

[jameswilson]

another sql question
how the hell do i pass date info in the statement as my motion query is too heavy on the client machine, mysql handles it fine but my fx53 struggles with the amount of data returned so i want to limit to a date value ie a first date second date thing

SELECT * FROM zm.Frames F
WHERE F.TimeStamp = date("2005/12/8")

only returns midnight exactly and i need all events from that date

cheers