[libdbi-users] driver issues and timezones
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. > While the supplied patch appears to work ok for TIMESTAMP WITH TIME ZONE columns, I had a problem with TIME WITH TIME ZONE columns. Turns out that the pgsql driver did not handle this column type correctly. I've fixed this here, but I'll run a few more tests to make sure all is fine now. I guess I'll be able to check in the changes tomorrow. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de --- 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&kid=120709&bid=263057&dat=121642 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] driver issues and timezones
On 5/16/06, Markus Hoenicka <[EMAIL PROTECTED]> wrote: Mike Rylander writes: > I've attached a patch against my local copy of the new file, so it > should patch CVS with out too much of a fight. I've added a little > logic to look for a ':' minute separator in the -8:00 format. It > compiles and runs for me, so please give it a whirl and make sure it > passes muster. > The patch works as advertized. However, I felt the same could be achieved with less code, and I started to reimplement the timezone handling by reversing the logic. Checking for a separator first allows a cleaner implementation imho. I've checked in the code, a patch against the pre-timezone dbd_helper.c is attached. Please check whether the current implementation works ok for your testcases. The patch works perfectly, and the logic is definitely more readable now. Thanks again! 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
Re: [libdbi-users] driver issues and timezones
Mike Rylander writes: > I've attached a patch against my local copy of the new file, so it > should patch CVS with out too much of a fight. I've added a little > logic to look for a ':' minute separator in the -8:00 format. It > compiles and runs for me, so please give it a whirl and make sure it > passes muster. > The patch works as advertized. However, I felt the same could be achieved with less code, and I started to reimplement the timezone handling by reversing the logic. Checking for a separator first allows a cleaner implementation imho. I've checked in the code, a patch against the pre-timezone dbd_helper.c is attached. Please check whether the current implementation works ok for your testcases. regards, Markus dbd_helper.c.patch Description: dbd_helper timezone patch V.2 -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de
[libdbi-users] driver issues and timezones
Hi Mike, Mike Rylander writes: > However, I'm having a problem using libdbi from within a shared > object. In much the same way that Apache loads DSOs, we have built a > server infrastructure that allows loading of application > implementation shared libraries. The problem is that in order for > libdbi to load a driver on demand we must link both libdbi proper > (normal) any driver we may want to use (eh?) against our > implementation libraries. Not only that, but we have to add an > LDFLAGS setting to the libdbi-drivers configure.in to tell it to link > against libdbi.so. > > I'm hoping that we're just overlooking something simple here, but > we've been pulling our hair out for the last couple days trying to > figure this out, and that's the only solution we could come up with. > I don't think you're missing something obvious here. See also a previous thread on the devel list: http://comments.gmane.org/gmane.comp.db.libdbi.drivers/7 (the SF archive is currently down. You'd see a few more posts of this thread) Linking the drivers against libdbi appears to be a hack that works in this situation. > [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. > At a glance the patch appears to do what it should. I'll give it a try with the other database engines that run on my box and check in the patch if it runs ok. Thanks for the code. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de --- 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&kid=120709&bid=263057&dat=121642 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
[libdbi-users] driver issues and timezones
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
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)
- 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
---
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&kid=120709&bid=263057&dat=121642
___
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] driver issues and timezones
On 5/16/06, Markus Hoenicka <[EMAIL PROTECTED]> wrote:
Mike Rylander writes:
> I'd be interested to see what the optimization was. :)
>
I've replaced
if (strlen(*cur)) {
with
if (*cur) {
Cool. Not sure why I didn't do that originally... :)
Saves us one function call. Whoohoo.
Besides, I've changed index() calls to strchr() calls just to make
the code consistent.
OK. I've continued with strchr() ... more on that below.
> > 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]
>
According to my tests both '-8:00' and '-08:00' work. '-0800' is
another option, whereas '-800' fails (correctly so). I did not change
the logic of your string parsing. Your patch is probably a bit more
forgiving than you thought. In any case, according to the PostgreSQL
docs all of the above notations that work conform to ISO8601.
Ahh.. I see. Unfortunately, I believe that there is a bug here in my
original patch. I'm looking at the total length of the timezone
string sans direction marker, and only if it is greater than 4 do I
assume there is a ':' minute separator. If the length is exactly 4
then I am assuming the zero padded, four digit format. This works
fine with complete hour offsets, but some timezones require half or
quarter hours, and the original patch won't work with those timezones
in the -8:00 format -- they will be split like this: -5:45 --> (5:,
5), instead of (5, 45).
I've attached a patch against my local copy of the new file, so it
should patch CVS with out too much of a fight. I've added a little
logic to look for a ':' minute separator in the -8:00 format. It
compiles and runs for me, so please give it a whirl and make sure it
passes muster.
Thanks again for taking quick action on this, and being open to
patches from complete outsiders. That's very encouraging!
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
libdbi-time_t-timezone-correction.patch
Description: Binary data
