sql statements

[jameswilson]

its ok got it i needed a between >= date 00:00:00 and <= 23:59:59

SELECT * FROM zm.Frames F
WHERE F.TimeStamp between ('2005/12/1 00:00:00') and ('2005/12/1 23:59:59')

[zoneminder]

Yes, although I never use between as I can't remember where the boundaries are so I would use

Code: Select all

... where StartTime >= "2005-12-05" and StartTime < "2005-12-06";

Using hypens rather than slashes also makes it clearer the format you are using and is what mysql uses itself.

Phil

[jameswilson]

i thought of that but i have made the app select a date from a calender control so, i wanted to avoid the problem of the end of the month as i wouldnt have been able to just add 1 day to the selected date as i have split them up so i can convert them from ms to mysql, it seems mysql uses dates backwards

[zoneminder]

I'm not sure I exactly understand the problem but you can also do

Code: Select all

... where StartTime >= somedate and StartTime < somedate + interval 1 day;

in case that's relevant.

Phil

[jameswilson]

if i use a datepicker in ms i get 29/12/05 so i split it up into individual parts ie day/month/year reorganise them as variables and pass into the sql string command
as its not a whole date i assumed that mysql would get it wrong, but then again whilst googling for sql help realised that mysql is a bit powerful aint it lol

james

[zoneminder]

Yes, for dates and times, http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html is your friend

Just be a little careful that mysql distinguishes between dates, times, datetimes and timestamps in some circumstances.

Phil

[jameswilson]

i know its all getting a bit complicated for me lol. im not looking forward the sql bit of the multicam playback code
lol

[zoneminder]

Me? I just love complicated db queries!

Phil

[jameswilson]

youll always have a job lol