Forum OpenACS Development: Date formats in OpenACS

Posted by Pascal Scheffers on 05/31/01 06:58 PM

While working on general-comments, I come across something that really bothers me: the date formats are HARD coded in queries. There is one very important difference in standard date formats: the US uses the really silly MM/DD/YYYY format, we Europeans use the correct DD/MM/YYYY order (well, we should have been using YYYY/MM/DD, but that is something else alltogether).

I think we should have site-wide (or maybe even user based) setting for date formats. My Dutch users will get very confused when 12-5-2001 is supposed to mean May 5th, 2001. To make it more interesting, my German neighbours like to read 5.12.2001.

It really pains me to port a query with a hard coded 'MM-DD-YYYY' in it. For starters we could get away with just using some (dummy) functions that supply the current format. The QueryDispatcher/DB-API should also be aware of date preferences, as it needs to substitute the SQL for the Queries. I can't think of a way to handle the stored-functions.

2: Response to Date formats in OpenACS (response to 1)

Posted by carl garland on 05/31/01 07:40 PM

Here is how I am dealing with date formats ... one way to solve not the only or best by far :)

I set the database and AOLserver to GMT and all times are stored in GMT.
I let each user set their own timezone
I also have timezone fields for each object / group.
When querying db I normalize the datetime to GMT accordingly by offset
I also let the user specify their preferred time format for viewing

I yanked most of the code for this functionality and forwarded it on to Ben awhile back. Its not pretty but should be pretty straigtht forward. Also if anyone is porting the calendar module and interested I will forward code that replaces all db access for the ad-calendar-widget.tcl file. Profiling code has produced about 90% increase for calls to procs. See post Here for more info.

Best Regards, Carl Garland

3: Response to Date formats in OpenACS (response to 1)

Posted by Pascal Scheffers on 05/31/01 08:00 PM

Storing everything in UTC and using an ad_prettydate and ad_localdate
would be *very* useful for me.

If there are no objections I will go ahead and make the queries for GC
return ANSI dateformat only and format those when displaying them
(using util_AnsiDatetoPrettyDate, for example)

4: Response to Date formats in OpenACS (response to 1)

Posted by Don Baccus on 06/01/01 02:07 PM

We should always use the pretty date convertor when displaying dates, because presumably everyone understands "June 1, 2001" or whatever.

Failure to do so is simple laziness on the part of the person who implemented the code in the first place.

As far as the timezone stuff mentioned by Carl goes, that's a good idea but I don't want to tackle it in a hodge-podge manner. And the porting effort's going a bit slowly as it is, so let's get that entered into the SDM as a feature request to be tackled after porting (and, Carl, if you want to think about a systematic approach for 4.x feel free to post about it).

5: Response to Date formats in OpenACS (response to 1)

Posted by Ed Avis on 06/01/01 02:43 PM

A good standard date format is YYYY-MM-DD (the ISO 8601 standard). Writing 'June' is okay, but you need to know English. But of course any such conversion should probably be encapsulated in a proc that produces pretty dates, and users should be able to change it. Probably best to retrieve the date from the database just in one single format (YYYY-MM-DD HH:MM:SS), and do any munging into the pretty date using a Tcl proc. That is more flexible and more portable.

6: Response to Date formats in OpenACS (response to 1)

Posted by carl garland on 06/01/01 03:17 PM

Since everyone is in the stage of porting I think that it would be preemptive to determine a generic procedure for displaying date/times now so everyone could use *that* procedure while they are porting instead of going back and replacing.

This could currently be just wrapped into PrettyDate now but I think it is very nice to allow the user to personalize the system and not force a format on them. What I envision for now would be something like


proc Display_Date { datetime {timezone 0} {format ""} {
    return [AnsiPrettyDate datetime]
}

But down the line we can rework it to add in the other features. Of course we could always just use AnsiPrettyDate and override it down the line but the proc name would be deceptive especially to people used to the other version. I would suggest to always retrieve the datetime in INTEGER form for easier manipulation and storing the timezone as offset but those introduce probs of their own. I will work up a more detailed discussion doc and post but since people are callling some sort of proc I think this would make things smoother when prob is attacked. Best Regards, Carl

7: Response to Date formats in OpenACS (response to 1)

Posted by Don Baccus on 06/01/01 03:48 PM

Datetime in Postgres isn't stored as an integer, but rather a string, so we're not going to retrieve it as an integer or numeric (which is how Oracle stores dates).

We should use to_char and "'YYYY-MM-DD'" when retrieving (assuming you want to truncate out the time portion) as it works in both Oracle and Postgres, and as I mentioned before use util_AnsiDatetoPrettyDate to do the conversion.

If you want a different name than the old 3.x name for the date formatting routine, that's fine. I don't see any reason to add a separate layer, though, we want *all* dates to be converted consistently within any given website.

So my recommendation would be to change the name, chase all references to util_AnsiDatetoPrettyDate (which is unwieldly anyway) and change them, and forget that this latter proc ever existed.

We could later add parameters to the core package to govern the actual format so those who are offended by "June 1, 2001" can pick a different one, etc. We certainly don't need this last bit of functionality by roll-out and we certainly don't need this slowing down our porting effort, though.

8: Response to Date formats in OpenACS (response to 1)

Posted by carl garland on 06/01/01 04:14 PM

> we want *all* dates to be converted consistently within any given website.

Exactly why while porting is being done everyone should depracate and use new proc where appropriate.

>We certainly don't need this last bit of functionality by roll-out and we certainly don't need this slowing down our porting effort, though.

Just thought it would be good time pick new name at let people know to use that *anytime* any datetime output is req. I agree other features should not detract from main porting. Since I am not really doing any porting just throwing in an idea that may save community effort. I agree with everything in Dons last post and recommend Don should be one to pick new name and let porters know.

Keep up good work, Carl

9: Response to Date formats in OpenACS (response to 1)

Posted by Ed Avis on 06/06/01 12:11 PM

I agree that dates should be selected from the database as a string
and not as a number or some other Oracleism. YYYY-MM-DD is an okay
format for just the date, but a better standard would be YYYY-MM-DD
HH:MM:SS. The extra time information can just be thrown away by the
date formatting procs if it is not needed.

10: Response to Date formats in OpenACS (response to 1)

Posted by carl garland on 06/08/01 06:10 PM

Just as a clarification the reason I recommended storing dates in integer (which is possible in PG using abstime and selecting using a cast ... even faster really since it doesn't have to convert to string) was for 2 reasons:

Speed ... see above
If you also have the users timezone stored as integer offset you can easily convert timestamps by simple addition and then apply whatever formatting the user prefers with the ns_fmttime call.

There are drawbacks to this approach most notably the range of legal times available. I do concede that this probably isn't the best implementation for OpenACS but still works and well for me.

Best Regards, Carl