Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Databases and dates

M

memco

macrumors 6502
Original poster
May 1, 2008
261
21
Couple questions about retrieving/displaying dates and times from a DB.

The DB is for a college, they have classes that occur once (possibly twice) a week at the same time each day. The time may span AM-PM. My question is how should I store the day and time info so that a) I can sort by day (Mon, Tue, Wed, etc.) and how can I store the time in one field so it's possible to tell if it's am/pm? I'd much rather use 24hr time, but it's a US school running on 12hr time so it doesn't make much sense to convert back and forth.

Further, I'm considering using an array to store the retrieved rows such that they're referenced record(j,i). In doing this though, I can't be sure which field I'm working with so it's hard to know how to filter/prepare records before display. So how do I handle filtering sorting?
 
On the topic of time, I'd just go with the 24-hour syntax. It'll cost the least headaches in the long run. It's very easy to just use a modulus operation on it as need be (time % 12).

Dealing with the day(s) of week that the class occurs is a little trickier. I'll have to give that some thought.

I'm kind of confused on your last paragraph, so not sure how to answer it.

Edit: OK, thought some more. For date occurrence you can do something along the lines of: MFW, TR, etc. to represent what days of the week they occur. Then for ordering them you can make a query such as,
Code: 
SELECT * FROM courses ORDER BY FIELD(days, 'MFW', 'TR', 'other');
and you can order the occurrences however you need, such as putting TR first.

Also, I'm assuming you're using MySQL.
 
Thanks Angelwatt. I'm using Access and ASP, but for the most part SQL statements translate to Access. I'm curious to know more about the FIELD(...) part. I'll be on Google...
 
memco said:
Thanks Angelwatt. I'm using Access and ASP, but for the most part SQL statements translate to Access. I'm curious to know more about the FIELD(...) part. I'll be on Google...
Click to expand...

Doesn't look like Access has a custom sorting function like MySQL. I did find a work around though that might help out.
Code: 
SELECT * FROM courses ORDER BY CASE days WHEN 'WMF' THEN 1 WHEN 'TR' THEN 2;
Taken from, http://www.eggheadcafe.com/community/aspnet/13/10032194/ms-access-sql-custom-orde.aspx though not tested. You would increase the number after the THEN part to create the order. Another page.
 
In general, most database formats have a DATETIME field in the format YYYY-MM-DD HH:MM:SS using the server local time zone. That makes it easy to match the format in your code, apply conditional sorts, etc., plus its very human readable when manually inspecting the database.

A less popular but widely used second method is to create an int(10) field storing the Unix timestamp of the current date/time based on GMT. GMT is used to avoid local time zone issues if the server is offsite or moved during a migration. Most well written sites allow the user via a profile table to select their timezone (actually the offset from GMT, accounting for DST) and the developer calculates the date/time the user sees in the web site. This method also allows for a much faster sorting by dates since its numerical on the database side, and uses less space when a large number of records are involved.

There are both code and database functions to create/convert between a timestamp and human readable format, as may be necessary for either approach.

FYI

-jim
 
Jim, let me see if I can break down how the system works (or should work anyhow), and see if you can help me.

We're a school on a quarter system so we have fall, winter, spring, and summer quarter for a year. Each quarter we have three different types of classes a student can take 10 week on ground, 6 week online (two tracks offered each quarter), or 3 day modulars. The on ground classes usually meet one day a week with three exceptions that meet two days a week.

The system we have now has rows like this:
Code: 
num, name, day, time, am_pm, prof, units, current, reg, audit, note

That is only for on ground classes (I'm trying to house all classes in one table if I can figure out a way).

All this to say that day is currently stored like "Monday" or "Tue./Thu", but this is hard to sort (and time is a pain too).

Our query right now (someone else's design) is
Code: 
SELECT * FROM onground WHERE [current] = "Yes" AND [day] = "Monday" ORDER BY am_pm, time

This is highly inefficient leading to six queries to make the full on ground schedule (each is currently stored in its own recordset). This design also makes an array difficult because time is stored in two separate fields.

I need to be able to take a table like this:
Code: 
num	name	day	time	am_pm	prof	units	current	reg	audit	note
====================================================================================
SAM1	Sample	Monday	9-11:45	am	Me	3	Yes	Yes	Yes	*
SAM2	Sample	Tue/Thu	5:30-6:50	pm	Me	3	Yes	Yes	Yes	
SAM3	Sample	Wednesd	7-9:45	pm	Me	3	Yes	Yes	Yes	
SAM4	Sample	TBA	TBA	 	Me	3	Yes	Yes	Yes	*

And sort it right such that I can get classes by the day and time they will occur:

Code: 
num	name	day	time	prof	units	note
======================================================
SAM1	Sample	Monday	9-11:45am	Me	3	*
SAM2	Sample	Tue/Thu	5:30-6:50pm	Me	3	
SAM3	Sample	Wednesd	7-9:45pm	Me	3	
SAM4	Sample	TBA	TBA	Me	3	*

Not to mention that this method doesn't account for online and modular courses. I'm working on a redesign such that all courses are kept in one table regardless of format.
 
I would use a "time" field to holdthe times. This is a special type. Most DBMS' have types like integer, string, time, currency. So just use the time type and then later you will find there are functions to return the day of the week or to add/subtract increments of time.

Internally hidden from you the time my be stored as (say) the number of seconds past Jan 1st 1960 or something like that.

As for the AM/PM thing. you are mixing storage format with display format. the two format do not have to be the same

If you need to store a schedule, I would use an enumerated type. You school likely only has a handfull of repee schedules like "m-w-f", "tu-th", "M', tu, .... you can last them so use an enumerated type. If the DBMS lacks enumerated types make a table that contains the enumerations
 
Just based on the time portion of the discussion, here is how I would likely lay out the table structure.
Code: 
CREATE TABLE courses (
  day VARCHAR(),
  start TIME,
  end TIME
);
Then, to achieve the output you want you can make a query like so,
Code: 
SELECT day,
  CONCAT(
    DATE_FORMAT(start, '%l'),     // hour
    IF(MINUTE(start)!='0',        // if minute not zero
      DATE_FORMAT(start, ':%i'),  // output minute portion
      ''),                        // or nothing
    '-',
    DATE_FORMAT(end, '%l:%i%p')   // ending time with hour, minute, am/pm
  ) AS time
  FROM courses;
The comments were added just for clarity. You said you figured out how to do the custom sort so you can add it as necessary. The above should output the class times as 5:30-7:45PM, 7-9:45PM. Though I didn't actually try the query out, but you can see the principle of what can be done. I haven't done much with Access so not sure what functions here may not transfer.
 
An update: I've solved the time issue. I actually have it as two fields, but now it's a start and an end time, and I'm concatenating the two in the query:
Code: 
SELECT (startTime & '-' & endTime) AS mTime FROM table ORDER BY startTime
(ASP uses & for concatenation whereas most use +).

What's still getting me is how to handle the days a class occurs on. Let's say I have something like this:

Code: 
Course	day
--------------
1	Mon
2	Tue
3	Tue/Thu
4	Tue
5	Wed
6	Thu
3	Tue/Thu
7	Thu

Course 3 is the clincher: it needs to appear in both spots. I can't wrap my head around what information I need to store in order to acheive this. I'm fairly sure I'll need two fields to do it, but outside of that I'm kind of stumped. Any ideas?

PS. on the off chance anyone else here uses Access, you may find this function reference helpful.
 
You mentioned before you figured out a way to do a custom order for these days of the week. Can you post that as a starting point? Also, I'm curious what purpose this will serve (how will it be used), namely having the same course appear twice in the result set. I just want to make sure you aren't doing anything unnecessary or over complicating it.
 
Code: 
SELECT IIf([[Active Schedule].day]] = "Mon", 1, 
	IIf([[Active Schedule].day]] = "Tue", 2, 
	IIf([[Active Schedule].day]] = "Tue/Thu", 2, 
	IIf([[Active Schedule].day]] = "Wed", 3, 
	IIf([[Active Schedule].day]] = "Thu", 4, 
	IIf([[Active Schedule].day]] = "Thu/Tue", 6, 
	IIf([[Active Schedule].day]] = "Fri", 7, 8)))))))
FROM table
ORDER BY [Active Schedule].day

This is to display a course schedule. You can see a working example at http://www.kingscollege.edu/pstudent2/registrar/course_schedule.asp.
 
To get a record to show up in two spots will require using two tables. You can create a days table,
Code: 
[B]Table: Days[/B]
c_id  day
1     Mon
2     Tue
3     Tue
3     Thu
4     Tue
5     Wed
6     Thu
7     Thu

[B]Query:[/B]
SELECT IIf([[Active Schedule].day]] = "Mon", 1, 
	IIf([[Active Schedule].day]] = "Tue", 2, 
	IIf([[Active Schedule].day]] = "Tue/Thu", 2, 
	IIf([[Active Schedule].day]] = "Wed", 3, 
	IIf([[Active Schedule].day]] = "Thu", 4, 
	IIf([[Active Schedule].day]] = "Thu/Tue", 6, 
	IIf([[Active Schedule].day]] = "Fri", 7, 8)))))))
FROM table, [COLOR="Blue"]days[/COLOR]
[B][COLOR="Blue"]WHERE table.course = days.c_id[/COLOR][/B]
ORDER BY [Active Schedule].day
You could pretty use your current query, but add the other table to the FROM list and add a WHERE clause to match the two tables up appropriately. You likely need to modify the IIF part to ensure it matches the new table's day column. I don't understand the Access syntax enough to change that.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back