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

And

2010-07-20T17:33:19Z

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?

Collapse
Posted by Torben Brosten on
Iuri,

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.

cheers,

Torben

Collapse
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.

http://www.tcl.tk/man/tcl8.4/TclCmd/clock.htm

Collapse
Posted by Iuri Sampaio on
Dave,

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.

Torben,
Thanks for the references!

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

Collapse
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"

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

Try:

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

cheers,

Torben

Collapse
Posted by Torben Brosten on
Free-form scan form of scan is deprecated http://www.tcl.tk/man/tcl/TclCmd/clock.htm#M80

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