Add Support for "24:00:00" with Time type

[Will]

The postgresql Time type supports times from "00:00:00" to "24:00:00" in accordance with ISO 8601.  The python datetime.time class does not currently support "24:00:00" but it would be useful to have SQLAlchemy support that.  I'm using SQLAlchemy 0.6.8 and was wondering if there is a way to allow it to support the "24:00:00" midnight notation.

I've tried to make a custom type that would support it, but it seems that psycopg2 will return a datetime.time class even if I define the custom type to implement the Text type.

from datetime import timedelta

from sqlalchemy.types import TypeDecorator, Text

class Time(timedelta):

    """Time class that supports times between "00:00:00" and "24:00:00"

    inclusive."""

    SEC_PER_MIN = 60

    SEC_PER_HOUR = SEC_PER_MIN * 60

    SEC_PER_DAY = SEC_PER_HOUR * 24

    def __new__(cls, hour=0, minute=0, second=0):

        seconds = hour * cls.SEC_PER_HOUR + minute * cls.SEC_PER_MIN + second

        instance = timedelta.__new__(cls, 0, seconds)

        if not (timedelta(0) <= instance <= timedelta(1)):

            raise ValueError('Values must be between 00:00:00 and 24:00:00.')

        return instance

    def __str__(self):

        return '{0.hour:02}:{0.minute:02}:{0.second:02}'.format(self)

    def __repr__(self):

        return '{0}({1.hour}, {1.minute}, {1.second})'.format(

                type(self).__name__, self)

    @property

    def hour(self):

        seconds = self.days * self.SEC_PER_DAY + self.seconds

        return seconds / self.SEC_PER_HOUR

    @property

    def minute(self):

        return self.seconds % self.SEC_PER_HOUR / self.SEC_PER_MIN

    @property

    def second(self):

        return self.seconds % self.SEC_PER_MIN

    @classmethod

    def parse(cls, time):

        return Time(*[int(x) for x in time.split(':')])

class TimeOfDay(TypeDecorator):

    """Time type that handles times between 00:00:00 and 24:00:00."""

    impl = Text

    def process_bind_param(self, value, dialect):

        """Store time as a string."""

        if value is None:

            return None

        return str(value)

    def process_result_value(self, value, dialect):

        """Return Time object."""

        if value is None:

            return None

        return Time.parse(value)

This would work if the actual database column was Text but in the postgres database the column is Time.  So it seems that psycopg2 converts that automatically to a datetime.time when retrieving the value, which makes the time "24:00:00" be "00:00:00".

Thanks for any input,

-Will Weaver

[Michael Bayer]

On Apr 18, 2012, at 9:36 AM, Will wrote:

The postgresql Time type supports times from "00:00:00" to "24:00:00" in accordance with ISO 8601.  The python datetime.time class does not currently support "24:00:00" but it would be useful to have SQLAlchemy support that.  

puzzled, looking at http://docs.python.org/library/datetime.html#datetime.time:

All arguments are optional. tzinfo may be None, or an instance of a tzinfo subclass. The remaining arguments may be ints or longs, in the following ranges:

• 0 <= hour < 24
• 0 <= minute < 60
• 0 <= second < 60
• 0 <= microsecond < 1000000.

?

the coercion of PG's date/time fields into Python objects are a product of psycopg2.   If psycopg2 isn't doing what you want here, you'd want to check with that product - psycopg2 has a comprehensive system of modifying it's typing behavior: http://initd.org/psycopg/docs/extensions.html#sql-adaptation-protocol-objects

I'm using SQLAlchemy 0.6.8 and was wondering if there is a way to allow it to support the "24:00:00" midnight notation.

SQLAlchemy doesn't deal with string notations when it talks to Postgresql regarding date and time types.    Psycopg2 handles the details of string formatting.

I've tried to make a custom type that would support it, but it seems that psycopg2 will return a datetime.time class even if I define the custom type to implement the Text type.

right, this is all psycopg2.   You'd need to establish this behavior using psycopg2 only first, by registering adapters as described in the above document.    Once you set that up SQLAlchemy just passes that data right through.

[Will]

Yes, I knew about the adapters.  A coworker of mine came up with this:

   ...

s = Session()
c = s.connection()

cast_time = lambda value, cur: Time.parse(value)
TIME = c.dialect.dbapi.extensions.new_type((1083,), "TIME", cast_time)

  c.dialect.dbapi.extensions.register_type(TIME)

I should have mentioned that in the first place.  I was wondering, what's the recommended way to modify the dialect settings globally, so they are used whenever a new engine/connection is created?

[Michael Bayer]

On Apr 18, 2012, at 10:47 AM, Will wrote:

Yes, I knew about the adapters.  A coworker of mine came up with this:

   ...

s = Session()
c = s.connection()

cast_time = lambda value, cur: Time.parse(value)
TIME = c.dialect.dbapi.extensions.new_type((1083,), "TIME", cast_time)

  c.dialect.dbapi.extensions.register_type(TIME)

I should have mentioned that in the first place.  I was wondering, what's the recommended way to modify the dialect settings globally, so they are used whenever a new engine/connection is created?

Since you're doing things that are psycopg2 specific, you could just import psycopg2:

from psycopg2 import extensions

extensions.register_type(...)

if you wanted to keep it local to an engine, you could do a "connect" event to add connection scope:

from psycopg2 import extensions

@event.listens_for(myengine, "connect")

def setup_time_type(dbapi_conn, conn_rec):

    extensions.register_type(TIME, dbapi_conn)

On Wednesday, April 18, 2012 10:17:43 AM UTC-4, Michael Bayer wrote:

On Apr 18, 2012, at 9:36 AM, Will wrote:

The postgresql Time type supports times from "00:00:00" to "24:00:00" in accordance with ISO 8601.  The python datetime.time class does not currently support "24:00:00" but it would be useful to have SQLAlchemy support that.  

puzzled, looking at http://docs.python.org/library/datetime.html#datetime.time:

All arguments are optional. tzinfo may be None, or an instance of a tzinfo subclass. The remaining arguments may be ints or longs, in the following ranges:

• 0 <= hour < 24
• 0 <= minute < 60
• 0 <= second < 60
• 0 <= microsecond < 1000000.

?

the coercion of PG's date/time fields into Python objects are a product of psycopg2.   If psycopg2 isn't doing what you want here, you'd want to check with that product - psycopg2 has a comprehensive system of modifying it's typing behavior: http://initd.org/psycopg/docs/extensions.html#sql-adaptation-protocol-objects

I'm using SQLAlchemy 0.6.8 and was wondering if there is a way to allow it to support the "24:00:00" midnight notation.

SQLAlchemy doesn't deal with string notations when it talks to Postgresql regarding date and time types.    Psycopg2 handles the details of string formatting.

I've tried to make a custom type that would support it, but it seems that psycopg2 will return a datetime.time class even if I define the custom type to implement the Text type.

right, this is all psycopg2.   You'd need to establish this behavior using psycopg2 only first, by registering adapters as described in the above document.    Once you set that up SQLAlchemy just passes that data right through.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Oa13nLlwW5YJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Will Weaver]

On Wed, Apr 18, 2012 at 10:57 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Apr 18, 2012, at 10:47 AM, Will wrote:

Yes, I knew about the adapters.  A coworker of mine came up with this:

   ...

s = Session()

c = s.connection()





cast_time = lambda value, cur: Time.parse(value)


TIME = c.dialect.dbapi.extensions.new_type((1083,), "TIME", cast_time)

  c.dialect.dbapi.extensions.register_type(TIME)

I should have mentioned that in the first place.  I was wondering, what's the recommended way to modify the dialect settings globally, so they are used whenever a new engine/connection is created?

Since you're doing things that are psycopg2 specific, you could just import psycopg2:

from psycopg2 import extensions

extensions.register_type(...)

if you wanted to keep it local to an engine, you could do a "connect" event to add connection scope:

from psycopg2 import extensions

@event.listens_for(myengine, "connect")

def setup_time_type(dbapi_conn, conn_rec):

    extensions.register_type(TIME, dbapi_conn)

Awesome, I'll try that out.  Thanks for the input, and as always thanks for the quick reply.

[Will Weaver]

For those that end up being interested in something like this.  For the complete round robin I did this.

from path.to.time import Time

from psycopg2 import extensions

def cast_time(value, cur):

    """Cast postgresql Time type to a Time object"""

    if value is None:

        return None

    return Time.parse(value)

# 1083 is the oid for postgres Time type

TIME = extensions.new_type((1083,), "TIME", cast_time)

extensions.register_type(TIME)

def adapt_time(value):

    """Adapt value coming in to something postgres can handle."""

    return extensions.adapt(str(value))

extensions.register_adapter(Time, adapt_time)