Re: #203: Timestamps "BC" raise "ValueError: second must be in 0..59"

Index: psycopg/adapter_mxdatetime.c
===================================================================
--- psycopg/adapter_mxdatetime.c	(revision 921)
+++ psycopg/adapter_mxdatetime.c	(working copy)
@@ -43,71 +43,70 @@
 static PyObject *
 mxdatetime_str(mxdatetimeObject *self)
 {
-    PyObject *str = NULL, *res = NULL;
-    PyObject *supa, *supb;
+    mxDateTimeObject *dt;
+    mxDateTimeDeltaObject *dtd;
+    char buf[128] = { 0, };
 
     switch (self->type) {
 
     case PSYCO_MXDATETIME_DATE:
-        str = PyObject_GetAttrString(self->wrapped, "date");
+        dt = (mxDateTimeObject *)self->wrapped;
+        if (dt->year >= 1)
+            PyOS_snprintf(buf, sizeof(buf) - 1, "'%04ld-%02d-%02d'",
+                          dt->year, (int)dt->month, (int)dt->day);
+        else
+            PyOS_snprintf(buf, sizeof(buf) - 1, "'%04ld-%02d-%02d BC'",
+                          1 - dt->year, (int)dt->month, (int)dt->day);
         break;
 
     case PSYCO_MXDATETIME_TIMESTAMP:
-        /* here we build the right ISO string from date and time */
-        supa = PyObject_GetAttrString(self->wrapped, "date");
-        supb = PyObject_GetAttrString(self->wrapped, "time");
-        str = PyString_FromFormat("%sT%s",
-            PyString_AsString(supa), PyString_AsString(supb));
-        Py_XDECREF(supa);
-        Py_XDECREF(supb);
+        dt = (mxDateTimeObject *)self->wrapped;
+        if (dt->year >= 1)
+            PyOS_snprintf(buf, sizeof(buf) - 1,
+                          "'%04ld-%02d-%02dT%02d:%02d:%09.6f'",
+                          dt->year, (int)dt->month, (int)dt->day,
+                          (int)dt->hour, (int)dt->minute, dt->second);
+        else
+            PyOS_snprintf(buf, sizeof(buf) - 1,
+                          "'%04ld-%02d-%02dT%02d:%02d:%09.6f BC'",
+                          1 - dt->year, (int)dt->month, (int)dt->day,
+                          (int)dt->hour, (int)dt->minute, dt->second);
         break;
 
     case PSYCO_MXDATETIME_TIME:
     case PSYCO_MXDATETIME_INTERVAL:
-        str = PyObject_Str(self->wrapped);
-
         /* given the limitation of the mx.DateTime module that uses the same
            type for both time and delta values we need to do some black magic
            and make sure we're not using an adapt()ed interval as a simple
            time */
-        if (PyString_Size(str) > 8 && PyString_AsString(str)[8] == ':') {
-            mxDateTimeDeltaObject *obj = (mxDateTimeDeltaObject*)self->wrapped;
+        dtd = (mxDateTimeDeltaObject *)self->wrapped;
+        if (0 <= dtd->seconds && dtd->seconds < 24*3600) {
+            PyOS_snprintf(buf, sizeof(buf) - 1, "'%02d:%02d:%09.6f'",
+                          (int)dtd->hour, (int)dtd->minute, dtd->second);
+        } else {
+            double ss = dtd->hour*3600.0 + dtd->minute*60.0 + dtd->second;
 
-            char buffer[8];
-            int i, j, x;
-
-            double ss = obj->hour*3600.0 + obj->minute*60.0 + obj->second;
-            int us = (int)((ss - floor(ss))*1000000);
-
-            for (i=1000000, j=0; i > 0 ; i /= 10) {
-                x = us/i;
-                us -= x*i;
-                buffer[j++] = '0'+x;
-            }
-            buffer[j] = '\0';
-
-            res = PyString_FromFormat("'%ld days %d.%s seconds'",
-                obj->day, (int)round(ss), buffer);
+            if (dtd->seconds >= 0)
+                PyOS_snprintf(buf, sizeof(buf) - 1, "'%ld days %.6f seconds'",
+                              dtd->day, ss);
+            else
+                PyOS_snprintf(buf, sizeof(buf) - 1, "'-%ld days -%.6f seconds'",
+                              dtd->day, ss);
         }
         break;
     }
 
-    if (str != NULL && res == NULL) {
-        res = PyString_FromFormat("'%s'", PyString_AsString(str));
-    }
-    Py_XDECREF(str);
-
-    return res;
+    return PyString_FromString(buf);
 }
 
-PyObject *
+static PyObject *
 mxdatetime_getquoted(mxdatetimeObject *self, PyObject *args)
 {
     if (!PyArg_ParseTuple(args, "")) return NULL;
     return mxdatetime_str(self);
 }
 
-PyObject *
+static PyObject *
 mxdatetime_conform(mxdatetimeObject *self, PyObject *args)
 {
     PyObject *res, *proto;
Index: psycopg/typecast.c
===================================================================
--- psycopg/typecast.c	(revision 921)
+++ psycopg/typecast.c	(working copy)
@@ -79,6 +79,13 @@
         *day = acc;
         cz += 1;
     }
+
+    /* Is this a BC date?  If so, adjust the year value.  Note that
+     * mx.DateTime numbers BC dates from zero rather than one.  The
+     * Python datetime module does not support BC dates at all. */
+    if (*len >= 2 && s[*len-2] == 'B' && s[*len-1] == 'C')
+        *year = 1 - (*year);
+
     if (t != NULL) *t = s;
 
     return cz;
@@ -123,6 +130,12 @@
             else if (cz == 3) *us = acc;
             acc = -1; cz = 4;
             break;
+        case ' ':
+        case 'B':
+        case 'C':
+            /* Ignore the " BC" suffix, if passed -- it is handled
+             * when parsing the date portion. */
+            break;
         default:
             acc = (acc == -1 ? 0 : acc*10) + ((int)*s - (int)'0');
             if (cz == 3) usd += 1;
Index: ChangeLog
===================================================================
--- ChangeLog	(revision 921)
+++ ChangeLog	(working copy)
@@ -1,3 +1,20 @@
+2008-01-16  James Henstridge  <[email protected]>
+
+	* tests/__init__.py (test_suite): add date tests to test suite.
+
+	* tests/test_dates.py: add tests for date/time typecasting and
+	adaption.
+
+	* psycopg/adapter_mxdatetime.c (mxdatetime_str): add support for
+	outputting BC dates (which involves switching them to one-based
+	dates).  Also remove broken handling of microseconds.
+
+	* psycopg/typecast.c (typecast_parse_date): if the string ends
+	with "BC" adjust the year value to be a zero-based BC value as
+	used by mx.DateTime (datetime doesn't support BC dates).
+	(typecast_parse_time): ignore ' ', 'B' and 'C' in time strings
+	rather than treating them as part of the seconds part of the time.
+
 2008-01-14  James Henstridge  <[email protected]>
 
 	* psycopg/typecast_array.c (typecast_array_scan): set an initial
Index: tests/__init__.py
===================================================================
--- tests/__init__.py	(revision 921)
+++ tests/__init__.py	(working copy)
@@ -6,6 +6,7 @@
 
 import bugX000
 import extras_dictcursor
+import test_dates
 import test_psycopg2_dbapi20
 import test_quote
 import test_transaction
@@ -15,6 +16,7 @@
     suite = unittest.TestSuite()
     suite.addTest(bugX000.test_suite())
     suite.addTest(extras_dictcursor.test_suite())
+    suite.addTest(test_dates.test_suite())
     suite.addTest(test_psycopg2_dbapi20.test_suite())
     suite.addTest(test_quote.test_suite())
     suite.addTest(test_transaction.test_suite())
Index: tests/test_dates.py
===================================================================
--- tests/test_dates.py	(revision 0)
+++ tests/test_dates.py	(revision 0)
@@ -0,0 +1,234 @@
+#!/usr/bin/env python
+import math
+import unittest
+
+import psycopg2
+import tests
+
+
+class CommonDatetimeTestsMixin:
+
+    def execute(self, *args):
+        conn = psycopg2.connect("dbname=%s" % tests.dbname)
+        curs = conn.cursor()
+        curs.execute(*args)
+        return curs.fetchone()[0]
+
+    def test_parse_date(self):
+        value = self.DATE('2007-01-01', None)
+        self.assertNotEqual(value, None)
+        self.assertEqual(value.year, 2007)
+        self.assertEqual(value.month, 1)
+        self.assertEqual(value.day, 1)
+
+    def test_parse_null_date(self):
+        value = self.DATE(None, None)
+        self.assertEqual(value, None)
+
+    def test_parse_time(self):
+        value = self.TIME('13:30:29', None)
+        self.assertNotEqual(value, None)
+        self.assertEqual(value.hour, 13)
+        self.assertEqual(value.minute, 30)
+        self.assertEqual(value.second, 29)
+
+    def test_parse_null_time(self):
+        value = self.TIME(None, None)
+        self.assertEqual(value, None)
+
+    def test_parse_datetime(self):
+        value = self.DATETIME('2007-01-01 13:30:29', None)
+        self.assertNotEqual(value, None)
+        self.assertEqual(value.year, 2007)
+        self.assertEqual(value.month, 1)
+        self.assertEqual(value.day, 1)
+        self.assertEqual(value.hour, 13)
+        self.assertEqual(value.minute, 30)
+        self.assertEqual(value.second, 29)
+
+    def test_parse_null_datetime(self):
+        value = self.DATETIME(None, None)
+        self.assertEqual(value, None)
+
+    def test_parse_null_interval(self):
+        value = self.INTERVAL(None, None)
+        self.assertEqual(value, None)
+
+
+class DatetimeTests(unittest.TestCase, CommonDatetimeTestsMixin):
+    """Tests for the datetime based date handling in psycopg2."""
+
+    def setUp(self):
+        self.DATE = psycopg2._psycopg.PYDATE
+        self.TIME = psycopg2._psycopg.PYTIME
+        self.DATETIME = psycopg2._psycopg.PYDATETIME
+        self.INTERVAL = psycopg2._psycopg.PYINTERVAL
+
+    def test_parse_bc_date(self):
+        # datetime does not support BC dates
+        self.assertRaises(ValueError, self.DATE, '00042-01-01 BC', None)
+
+    def test_parse_bc_datetime(self):
+        # datetime does not support BC dates
+        self.assertRaises(ValueError, self.DATETIME,
+                          '00042-01-01 13:30:29 BC', None)
+
+    def test_parse_time_microseconds(self):
+        value = self.TIME('13:30:29.123456', None)
+        self.assertEqual(value.second, 29)
+        self.assertEqual(value.microsecond, 123456)
+
+    def test_parse_datetime_microseconds(self):
+        value = self.DATETIME('2007-01-01 13:30:29.123456', None)
+        self.assertEqual(value.second, 29)
+        self.assertEqual(value.microsecond, 123456)
+
+    def test_parse_interval(self):
+        value = self.INTERVAL('42 days 12:34:56.123456', None)
+        self.assertNotEqual(value, None)
+        self.assertEqual(value.days, 42)
+        self.assertEqual(value.seconds, 45296)
+        self.assertEqual(value.microseconds, 123456)
+
+    def test_parse_negative_interval(self):
+        value = self.INTERVAL('-42 days -12:34:56.123456', None)
+        self.assertNotEqual(value, None)
+        self.assertEqual(value.days, -43)
+        self.assertEqual(value.seconds, 41103)
+        self.assertEqual(value.microseconds, 876544)
+
+    def test_adapt_date(self):
+        from datetime import date
+        value = self.execute('select (%s)::date::text',
+                             [date(2007, 1, 1)])
+        self.assertEqual(value, '2007-01-01')
+
+    def test_adapt_time(self):
+        from datetime import time
+        value = self.execute('select (%s)::time::text',
+                             [time(13, 30, 29)])
+        self.assertEqual(value, '13:30:29')
+
+    def test_adapt_datetime(self):
+        from datetime import datetime
+        value = self.execute('select (%s)::timestamp::text',
+                             [datetime(2007, 1, 1, 13, 30, 29)])
+        self.assertEqual(value, '2007-01-01 13:30:29')
+
+    def test_adapt_timedelta(self):
+        from datetime import timedelta
+        value = self.execute('select extract(epoch from (%s)::interval)',
+                             [timedelta(days=42, seconds=45296,
+                                        microseconds=123456)])
+        seconds = math.floor(value)
+        self.assertEqual(seconds, 3674096)
+        self.assertEqual(int(round((value - seconds) * 1000000)), 123456)
+
+    def test_adapt_megative_timedelta(self):
+        from datetime import timedelta
+        value = self.execute('select extract(epoch from (%s)::interval)',
+                             [timedelta(days=-42, seconds=45296,
+                                        microseconds=123456)])
+        seconds = math.floor(value)
+        self.assertEqual(seconds, -3583504)
+        self.assertEqual(int(round((value - seconds) * 1000000)), 123456)
+
+
+# Only run the datetime tests if psycopg was compiled with support.
+if not hasattr(psycopg2._psycopg, 'PYDATETIME'):
+    del DatetimeTests
+
+
+class mxDateTimeTests(unittest.TestCase, CommonDatetimeTestsMixin):
+    """Tests for the mx.DateTime based date handling in psycopg2."""
+
+    def setUp(self):
+        self.DATE = psycopg2._psycopg.MXDATE
+        self.TIME = psycopg2._psycopg.MXTIME
+        self.DATETIME = psycopg2._psycopg.MXDATETIME
+        self.INTERVAL = psycopg2._psycopg.MXINTERVAL
+
+    def test_parse_bc_date(self):
+        value = self.DATE('00042-01-01 BC', None)
+        self.assertNotEqual(value, None)
+        # mx.DateTime numbers BC dates from 0 rather than 1.
+        self.assertEqual(value.year, -41)
+        self.assertEqual(value.month, 1)
+        self.assertEqual(value.day, 1)
+
+    def test_parse_bc_datetime(self):
+        value = self.DATETIME('00042-01-01 13:30:29 BC', None)
+        self.assertNotEqual(value, None)
+        # mx.DateTime numbers BC dates from 0 rather than 1.
+        self.assertEqual(value.year, -41)
+        self.assertEqual(value.month, 1)
+        self.assertEqual(value.day, 1)
+        self.assertEqual(value.hour, 13)
+        self.assertEqual(value.minute, 30)
+        self.assertEqual(value.second, 29)
+
+    def test_parse_time_microseconds(self):
+        value = self.TIME('13:30:29.123456', None)
+        self.assertEqual(math.floor(value.second), 29)
+        self.assertEqual(
+            int((value.second - math.floor(value.second)) * 1000000), 123456)
+
+    def test_parse_datetime_microseconds(self):
+        value = self.DATETIME('2007-01-01 13:30:29.123456', None)
+        self.assertEqual(math.floor(value.second), 29)
+        self.assertEqual(
+            int((value.second - math.floor(value.second)) * 1000000), 123456)
+
+    def test_parse_interval(self):
+        value = self.INTERVAL('42 days 05:50:05', None)
+        self.assertNotEqual(value, None)
+        self.assertEqual(value.day, 42)
+        self.assertEqual(value.hour, 5)
+        self.assertEqual(value.minute, 50)
+        self.assertEqual(value.second, 5)
+
+    def test_adapt_time(self):
+        from mx.DateTime import Time
+        value = self.execute('select (%s)::time::text',
+                             [Time(13, 30, 29)])
+        self.assertEqual(value, '13:30:29')
+
+    def test_adapt_datetime(self):
+        from mx.DateTime import DateTime
+        value = self.execute('select (%s)::timestamp::text',
+                             [DateTime(2007, 1, 1, 13, 30, 29.123456)])
+        self.assertEqual(value, '2007-01-01 13:30:29.123456')
+
+    def test_adapt_bc_datetime(self):
+        from mx.DateTime import DateTime
+        value = self.execute('select (%s)::timestamp::text',
+                             [DateTime(-41, 1, 1, 13, 30, 29.123456)])
+        self.assertEqual(value, '0042-01-01 13:30:29.123456 BC')
+
+    def test_adapt_timedelta(self):
+        from mx.DateTime import DateTimeDeltaFrom
+        value = self.execute('select extract(epoch from (%s)::interval)',
+                             [DateTimeDeltaFrom(days=42,
+                                                seconds=45296.123456)])
+        seconds = math.floor(value)
+        self.assertEqual(seconds, 3674096)
+        self.assertEqual(int(round((value - seconds) * 1000000)), 123456)
+
+    def test_adapt_megative_timedelta(self):
+        from mx.DateTime import DateTimeDeltaFrom
+        value = self.execute('select extract(epoch from (%s)::interval)',
+                             [DateTimeDeltaFrom(days=-42,
+                                                seconds=45296.123456)])
+        seconds = math.floor(value)
+        self.assertEqual(seconds, -3583504)
+        self.assertEqual(int(round((value - seconds) * 1000000)), 123456)
+
+
+# Only run the mx.DateTime tests if psycopg was compiled with support.
+if not hasattr(psycopg2._psycopg, 'MXDATETIME'):
+    del mxDateTimeTests
+
+
+def test_suite():
+    return unittest.TestLoader().loadTestsFromName(__name__)
+