Forum OpenACS Development: convert any date string to timestamp w/ or w/o timezone

Request notifications

Hi there,

I'm getting xml and rss feeds and putting the data into a database. I've run into two different date formats so far...

Wed, 21 Jul 2010 00:28:50 GMT



I'm sure there will be more. The field datatype I created is timestamptz,

Is there an existing function to convert any date strings to timestamp with or without time zone, such as 2011-11-19 23:24:46+00?

Posted by Torben Brosten on

You might want to look at ec_formatted_date and ec_formatted_full_date in ecommerce/tcl/ecommerce-utilities-procs.tcl

ecommerce-widgets-procs.tcl has some conversions also.



Posted by Dave Bauer on
The first thing to try is using

clock scan $date_string to see if Tcl can intepret it.

If that works you are all set and you can use clock format to convert to any format you like.

Posted by Iuri Sampaio on

Yes, I tried so. As in:

set date "Wed, 31 Oct 2012 16:03:55 -0200"
set date [clock format [clock scan $date] -format "%D %T"]

However I got a TCL exception on the second line.

Thanks for the references!

Anyway, I'll spend more neurons to solve it.

Posted by Iuri Sampaio on
I just remembered that I could make things easier by posting the TCL exception erro message.

I wonder if I store the date as varchar instead of timestamptz would facilitate the work, since I have no idea what formats might appear on future feeds.

What do you guys suggest?

[31/Oct/2012:18:40:44][6204.3050265456][-sched:7-] Notice: DATE {Wed, 31 Oct 2012 18:39:57 +0000}
[31/Oct/2012:18:40:44][6204.3050265456][-sched:7-] Error: Tcl exception:
unable to convert date-time string "Wed, 31 Oct 2012 18:39:57 +0000"
while executing
"clock scan $date"
(procedure "iurix_mail::new" line 29)
invoked from within
"iurix_mail::new -user_id $user_id -largs $largs "
(procedure "::callback::acs_mail_lite::incoming_email::impl::iurix-mail" line 27)
invoked from within
"::callback::acs_mail_lite::incoming_email::impl::iurix-mail -array email"
("uplevel" body line 1)
invoked from within
"::uplevel 1 $procname $args"

Posted by Jim Lynch on
Seems you're getting close to what you want; good hunting!
Posted by Torben Brosten on
Hi Iuri,


clock scan $date -format "%a, %d %b %Y %T %z"



Posted by Torben Brosten on
Free-form scan form of scan is deprecated

It looks like a proc that tests an ordered list of common formats would help for cases like this.