On 5/15/06, Markus Hoenicka <[EMAIL PROTECTED]> wrote:
Mike Rylander writes:
> [UPDATE] While my coworker was investigating the problem
> described above further, I've developed (and attached) a patch to
> dbd_helper.c to parse ISO8601 timezone offsets inside
> _dbd_parse_datetime() if they happen to occur. I use Postgres as my
> main database, and this allows me to use TIMESTAMP WITH TIME ZONE
> columns and get the correct date and time out, without having to
> mangle the timezone on the system (or inside the database) to assume
> UTC. It is untested with other databases, but it's fairly protective,
> looking for specific timezone markers, and should be able to handle
> any standard ISO timezone offset.
>
I've applied this patch (with one micro-optimization). I'll have to
I'd be interested to see what the optimization was. :)
add some information to the driver docs in order to explain how
temporal types are handled. For the time being, the following
description must suffice:
- you can pass time and datetime values with a numeric timezone
suffix. The suffix starts with '-' or '+'. The timezone can be given
either as hours only ('-8'), as hours and minutes ('-800'), or as
hours and minutes separated by a colon ('-8:00'). Other forms,
e.g. strings like 'PST', are currently not supported. Examples:
04:05:06-08:00 (time)
1999-01-08 04:05:06 -8:00 (timestamp)
It may just be a typo, but this is incorrect based on my original
patch. I followed ISO8601 format for using numeric timezone
extentions on a timestamp, which requires the format of
{+|-}hh[:mm]
Postgresql seems to drop the ':' from timezones from within the psql
shell, so I am checking for that permutation as well.
- some database engines support timezones by default and provide no
special column type. sqlite and sqlite3 use a string notation
internally and therefore have no problems with timezones.
- some database engines use specific column types to honor a timezone
suffix. This is true for pgsql. Use TIMESTAMP WITH TIME ZONE
instead of TIMESTAMP and TIME WITH TIME ZONE instead of TIME if you
want to use timezone suffixes.
- some database engines ignore the timezone suffix. This is true for
mysql.
- I can't tell how the other supported database engines and their
drivers handle timezones. Do the driver maintainers listen?
- due to the differences between the implementations, the use of
timezone data creates a portability problem. If your application is
supposed to support all available libdbi drivers, it may be
necessary to do the math inside your application before you store
temporal data.
regards,
Markus
--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0709&bid&3057&dat1642
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
