Forum OpenACS Q&A: 4.6.3 ecommerce belches request-error on ugly_date

AOLserver[1] is returning a "Request Error" about an ugly_date problem using PostgreSQL 7.2 from url[2]. This error is somewhat similar to the one described near the end of this[3] thread starting with this message[4], except that here, date-time is appending/including what appears to be a decimal value. Here is a snip from another instance of the same error (to show pattern):

unable to convert date-time string "2003-10-03 03:18:39.617515"
    while executing
"clock scan $ugly_date"
    (procedure "ec_formatted_full_date" line 9)
    invoked from within

Is there a config setting somewhere that fixes this, or?

Torben

1. . running OpenACS 4.6.3 with ecommerce from cvs oacs-4-6-3-final circa August 2003, on freebsd 4.x using posgres7.2
2. . https://localhost:8443/ecommerce/admin/orders/one?order%5fid=3000003
3. . https://openacs.org/forums/message-view?message_id=33302
4. https://openacs.org/forums/message-view?message_id=33447
5. Request Error details:

   unable to convert date-time string "2003-09-25 17:14:00.152482"
       while executing
   "clock scan $ugly_date"
       (procedure "ec_formatted_full_date" line 9)
       invoked from within
   "ec_formatted_full_date $confirmed_date"
       invoked from within
   "doc_body_append "
       [ad_admin_header "One Order"]
   
       <h2>One Order</h2>
   
       [ad_admin_context_bar [list "../" "Ecommerce([ec_system_name])"] [lis..."
       ("uplevel" body line 28)
       invoked from within
   "uplevel {
       	  ad_page_contract {
   
       Display one order.
   
       @author Eve Andersson (eveander@arsdigita.com)
       @creation-date Summer 1999
       @au..."
       (procedure "code::tcl::/usr/local/www/localhost/openacs-4-6/packages/eco..." line 2)
       invoked from within
   "code::tcl::$__adp_stub"
       invoked from within
   "if { [file exists $__adp_stub.tcl] } {
   
         # ensure that data source preparation procedure exists and is up-to-date
         adp_init tcl $__adp_stub
   ..."
       ("uplevel" body line 3)
       invoked from within
   "uplevel {
   
       if { [file exists $__adp_stub.tcl] } {
   
         # ensure that data source preparation procedure exists and is up-to-date
         adp_init t..."
       (procedure "adp_prepare" line 2)
       invoked from within
   "adp_prepare "
       (procedure "template::adp_parse" line 30)
       invoked from within
   "template::adp_parse [file root [ad_conn file]] {}"
       (procedure "adp_parse_ad_conn_file" line 7)
       invoked from within
   "$handler"
       ("uplevel" body line 2)
       invoked from within
   "uplevel $code"
       invoked from within
   "ad_try {
   	$handler
         } ad_script_abort val {
   	# do nothing
         }"
       invoked from within
   "rp_serve_concrete_file [ad_conn file]"
       (procedure "rp_serve_abstract_file" line 60)
       invoked from within
   "rp_serve_abstract_file "$root/$path""
       ("uplevel" body line 2)
       invoked from within
   "uplevel $code"
       invoked from within
   "ad_try {
   	rp_serve_abstract_file "$root/$path"
   	set tcl_url2file([ad_conn url]) [ad_conn file]
   	set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."
   

that would be a solution. Another maybe less involved is to apply the following (untested) patch:

Index: ecommerce-utilities-procs.tcl
===================================================================
RCS file: /cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-utilities-procs.tcl,v
retrieving revision 1.12
diff -r1.12 ecommerce-utilities-procs.tcl
568a569
<blockquote>    regsub -- {\.[0-9]+$} $ugly_date "" ugly_date
</blockquote>
590a592
<blockquote>    regsub -- {\.[0-9]+$} $ugly_date "" ugly_date
</blockquote>

This will strip the sub-second portion from the timestamp.

/Bart

'clock scan' is unable to process fractional time values (nanoseconds) from pg7.2.

These URLs address similar problems:

https://openacs.org/irc/log/2003-10-19
https://openacs.org/bugtracker/openacs/bug?bug_number=595
https://openacs.org/bugtracker/openacs/bug?bug_number=985
https://openacs.org/forums/message-view?message_id=33632

and for reference:

http://www.postgresql.org/docs/7.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
http://www.postgresql.org/docs/7.2/static/datatype-datetime.html

Given that I will gladly re-install with fresh DB if necessary..

What is the best (recipe style, or monkey-see monkey-do) way to approach this?

Would changing *all* cases in ecommerce of the sql time functions to integer seconds, ie current_timestamp(0) current_time(0) work (with re-install)?

Or, should I stick to changing just the references that affect 'clock scan', by changing for example current_timestamp to current_timestamp::timestamp(0) ?

ps. I would be glad to do 'worker-bee' work (including testing pg version) with this package.

It sounds like we have 3 choices for fixing this problem.

1. Have each query for each page format the date in the sql.
2. Put a regsub into ec_formatted_date procs and use those consistently in the tcl code.
3. Changing the data model to timestamp(0) without time zone.

I think I agree with Bart. #1 may be the "Right" thing to do but we stand a huge chance of introducing random bugs and its too much effort for the value.

#3 does seem like a better solution then #2.

Thanks Bart.

Caroline

Given that the OpenACS 4.6.3 stable requires pg 7.2.3[1], shouldn't a version of ecommerce be made available that works without modification?

I can grab another copy from CVS to start fresh, then make changes that get the code working with 4.6.3 and share it (back to CVS?), before modifying it for implementation purposes.

Also, yes, I see how ec could benefit from using ticket tracker, templating, news, and the openacs object abstractions. To this end, I am focusing on ecommerce for this stage of learning openacs by developing.

1. https://openacs.org/doc/openacs-4-6-3/individual-programs.html