Learning Coldfusion - time problem with "11pm - after midnight"

[MIDIman]

Hi all - sitting here learning CF on a rogue server out of my apartment. I have an issue where a certain query is returning no data, but should be.

I'm creating a "schedule." The times in the database are currently a bit wrong, so to speak, in that they are returned to CF for example as "1899-12-30 18:30:22." i.e. that would be 6:30 pm and 22 seconds.

When the numbers get to midnight, its not 24:00:00, but 00:00:00.

My query is currently an if, then statement looking for ranges like 01:55:00 - 02:05:00 to grab a certain entry. But when it gets to midnight (i.e. 23:55:00 - 00:05:00) it returns nothing.

See example code below. Here I'm trying to snag an approximately 30 minute entry in duration (between 24 minutes and 40 minutes) and the Start Time is between 11:55 pm and 12:18 am. Day of the week is not an issue, as I'm displaying this as a row across rather than down a column.

<CFOUTPUT query="schedule">
<cfif (Duration gt "0:24:00") and (Duration lt "0:40:00") and (Time gt "1899-12-30 23:55:00") and (Time lt "1899-12-30 00:18:00")>
<td width="12%" rowspan="1">
#EpisodeName#
</td>
</cfif>
</CFOUTPUT>

 

[kamper]

I think you've got a logical error there. Anything after 23:59 is actually the next day, so it should read:

<cfif (Duration gt "0:24:00") and (Duration lt "0:40:00") and (Time gt "1899-12-30 23:55:00") and (Time lt "1899-12-31 00:18:00")>

Did I interpret the problem correctly?

 

[MIDIman]

Did I interpret the problem correctly?

Well - actually I have another field with only the date. I didn't know that these return with both date and time when I was designing the access database. i.e. ALL times within the Date field are listed with the time as "00:00:00' and ALL dates in the Time field have their date as "1899-12-30".

So considering that date doesn't matter since its basically in sync with the Index, is there any way to do this?

As an example, looking at the data, I'm trying to select a row that has:

Time
00:17:16

Duration
00:28:27

 

[kamper]

So you're trying to do a comparison on time only, ignoring the date? Aside from the obvious: refactor to use proper datatypes, you would have to code a special case for when the duration pushes past midnight and increment date field by a day (even though you don't otherwise use it). I'd seriously try change datatypes, though. Doing comparisons on undefined dates just sounds like a recipe for bugs.

 

[notfred]

what is wrong with something like this?

for(int ii=0; ii < 24; ii++){
starttime = ii + ":00:00";
endtime = ii + ":59:59";
// run query or whatever
}

I don't know coldfusion specifically, but this gives you a bunch of 1 hour ranges starting at midnight one day, and ending at 11:59:59PM the same day.

 

[MIDIman]

I don't know coldfusion specifically, but this gives you a bunch of 1 hour ranges starting at midnight one day, and ending at 11:59:59PM the same day.

Thanks - I actually need just those 10 minutes or so between 11:55 and just after midnight, rather than the entire day. Are there any for-loops in CF?

I'd seriously try change datatypes, though. Doing comparisons on undefined dates just sounds like a recipe for bugs.

Just as an example, what datatype would you change to? This is starting to make sense.

 

[kamper]

Well, ideally you'd use both the date and time field in your current setup. If that doesn't fit your data model then there should be data types that only store a date and only store a time. If that doesn't exist it's time to get a new database. There are plenty of free ones that are much better than access.

 

[MrChad]

Maybe:

<cfif Minute(Duration) GT 24 and Minute(Duration) LT 40>
...<cfif (Hour(ParseDateTime(Time)) EQ 23 and Minute(ParseDateTime(Time)) GT 55) or (Hour(ParseDateTime(Time)) EQ 0 and Minute(ParseDateTime(Time)) LT 18)>
...</cfif>
</cfif>

Of course, you'll probably want to check the format of your Duration and Time fields to make sure that your DateTime functions don't blow up.

 

[MIDIman]

Originally posted by: kamper
Well, ideally you'd use both the date and time field in your current setup. If that doesn't fit your data model then there should be data types that only store a date and only store a time. If that doesn't exist it's time to get a new database. There are plenty of free ones that are much better than access.

Perhaps then this needs further investigation - here are some boring details...

I'm creating a semi-schedule of TV programming, say for a website that displays a week's worth of TV programming. The data is coming from a tab-delimited text file and I'm linking to that in an access database. The idea is that X-worker can simply replace the text file on the server and all data will be updated in both the Access database and online - thus, I want to have no editing on the backend, and I have to work with the data that I have.

The data is indexed by Access, and sorted first by Date, then Time.

The table is setup with Monday - Sunday up top, and times are going down the left side via 30 minute rows. A single day is actually 9:00am to 2:00am the following day! The data is also very screwy - i.e. a 30 minute program could be 43 minutes or 22 minutes, so I have to search durations and then figure out how many rows to span (1 for 30 minutes, 2 for an hour, 4 for 2 hours, etc). The start times are also screwy - a 10pm program could start at 9:56pm or 10:10pm - so I'm sorting through that as well. Not to mention that there are gaps that are covered by tiny little PSA's - anywhere from 15 seconds to sometimes 15 minutes. Going with duration has done a good job of filtering that.


The problem is that in HTML, you have to develop table cells across rows rather than down columns, so Date is almost useless to me. Instead, I'm grabbing all programs for a specific time, and outputting them across. Date is again useless because I want to update the data each week via a text file upload and I don't want to have to edit the cfm every week.

Without the database knowing what a Monday is, how can I query for it when the date's data is changing in the database every week? I thought I could maybe use the client's computer's date-time, but isn't that a bit unreliable?


...I don't know, clearly I need to think through the logic a bit more, sit with some brainstorming, and probably learn Coldfusion and Querying a bit more thoroughly since I've never used either! I just haven't gone through the programming mind in a very long time, so I'm a bit rusty.

 

[MIDIman]

Originally posted by: MrChad
Maybe:

<cfif Minute(Duration) GT 24 and Minute(Duration) LT 40>
...<cfif (Hour(ParseDateTime(Time)) EQ 23 and Minute(ParseDateTime(Time)) GT 55) or (Hour(ParseDateTime(Time)) EQ 0 and Minute(ParseDateTime(Time)) LT 18)>
...</cfif>
</cfif>

Of course, you'll probably want to check the format of your Duration and Time fields to make sure that your DateTime functions don't blow up.

Thanks for this - might try this out.

 

[MIDIman]

LOl - got it. I think I had tried this before, but had used AND instead of OR. Here's what I ended up with. Yeah, its all screwy - but at least I have something that works for the current data, and I can work from there in terms of formatting, etc.

Thanks all for your help - you'll probably be seeing more posts from me in the future!

<cfif ((Duration gt "0:24:00") and (Duration lt "0:40:00")) and

(((Time eq "1899-12-30 00:00:00")) or
((Time gt "1899-12-30 23:55:00") and (Time lt "1899-12-30 23:59:59")) or
((Time gt "1899-12-30 00:00:00") and (Time lt "1899-12-30 00:18:00")))>