Kirill Simonov avatar Kirill Simonov committed ef7a126

Added support for time and datetime types, functions and operators.

Comments (0)

Files changed (32)

src/htsql/domain.py

           (?: (?P<tz_utc> Z ) |
               (?P<tz_sign> [+-] )
               (?P<tz_hour> \d{1,2} )
-              (?: :
+              (?: :?
                   (?P<tz_minute> \d{2} )
               )? )
         )?
         elif tz_sign is not None:
             tz_hour = int(tz_hour)
             tz_minute = int(tz_minute) if tz_minute is not None else 0
-            offset = tz_hour+60+tz_minute
+            offset = tz_hour*60+tz_minute
             if tz_sign == '-':
                 offset = -offset
             tz = FixedTZ(offset)

src/htsql/fmt/html.py

 from .format import Format, Formatter, Renderer
 from .entitle import entitle
 from ..domain import (Domain, BooleanDomain, NumberDomain,
-                      StringDomain, EnumDomain, DateDomain)
+                      StringDomain, EnumDomain, DateDomain,
+                      TimeDomain, DateTimeDomain)
 import cgi
 
 
         return str(value)
 
 
+class FormatTime(Format):
+
+    adapts(HTMLRenderer, TimeDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return self.format_null()
+        return str(value)
+
+
+class FormatDateTime(Format):
+
+    adapts(HTMLRenderer, DateTimeDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return self.format_null()
+        if not value.time():
+            return str(value.date())
+        return str(value)
+
+

src/htsql/fmt/json.py

 from ..adapter import Adapter, adapts
 from .format import Format, Formatter, Renderer
 from ..domain import (Domain, BooleanDomain, NumberDomain, FloatDomain,
-                      StringDomain, EnumDomain, DateDomain)
+                      StringDomain, EnumDomain, DateDomain, TimeDomain,
+                      DateTimeDomain)
 from .entitle import entitle
 import re
 
         return escape(str(value))
 
 
+class FormatTime(Format):
+
+    adapts(JSONRenderer, TimeDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return "null"
+        return escape(str(value))
+
+
+class FormatDateTime(Format):
+
+    adapts(JSONRenderer, DateTimeDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return "null"
+        if not value.time():
+            return escape(str(value.date()))
+        return escape(str(value))
+
+
 class EntitleDomain(Adapter):
 
     adapts(Domain)
     name = "date"
 
 
+class EntitleTime(EntitleDomain):
+
+    adapts(TimeDomain)
+    name = "time"
+
+
+class EntitleDateTime(EntitleDomain):
+
+    adapts(DateTimeDomain)
+    name = "datetime"
+
+
 class Escape(object):
 
     escape_pattern = r"""[\x00-\x1F\\/"]"""

src/htsql/fmt/spreadsheet.py

 from ..adapter import adapts
 from .format import Format, Formatter, Renderer
 from ..domain import (Domain, BooleanDomain, NumberDomain, FloatDomain,
-                      StringDomain, EnumDomain, DateDomain)
+                      StringDomain, EnumDomain, DateDomain, TimeDomain,
+                      DateTimeDomain)
 from .entitle import entitle
 import csv
 import cStringIO
         return str(value)
 
 
+class FormatTime(Format):
+
+    adapts(CSVRenderer, TimeDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return ""
+        return str(value)
+
+
+class FormatDateTime(Format):
+
+    adapts(CSVRenderer, DateTimeDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return ""
+        if not value.time():
+            return str(value.date())
+        return str(value)
+
+

src/htsql/fmt/text.py

 from .entitle import entitle
 from ..domain import (Domain, BooleanDomain, NumberDomain, IntegerDomain,
                       DecimalDomain, FloatDomain, StringDomain, EnumDomain,
-                      DateDomain)
+                      DateDomain, TimeDomain, DateTimeDomain)
 import re
 import decimal
 import datetime
         return ["%*s" % (-width, value)]
 
 
+class FormatTime(Format):
+
+    adapts(TextRenderer, TimeDomain)
+
+    def measure(self, value):
+        if value is None:
+            return 0
+        return len(str(value))
+
+    def __call__(self, value, width):
+        assert isinstance(value, maybe(datetime.time))
+        if value is None:
+            return self.format_null(width)
+        return ["%*s" % (-width, value)]
+
+
+class FormatDateTime(Format):
+
+    adapts(TextRenderer, DateTimeDomain)
+
+    def measure(self, value):
+        if value is None:
+            return 0
+        if not value.time():
+            return 10
+        return len(str(value))
+
+    def __call__(self, value, width):
+        assert isinstance(value, maybe(datetime.datetime))
+        if value is None:
+            return self.format_null(width)
+        if not value.time():
+            return ["%*s" % (-width, value.date())]
+        return ["%*s" % (-width, value)]
+
+

src/htsql/tr/coerce.py

 from ..adapter import Adapter, adapts, adapts_many
 from ..domain import (Domain, VoidDomain, TupleDomain, UntypedDomain,
                       BooleanDomain, IntegerDomain, DecimalDomain, FloatDomain,
-                      StringDomain, EnumDomain, DateDomain, OpaqueDomain)
+                      StringDomain, EnumDomain, DateDomain, TimeDomain,
+                      DateTimeDomain, OpaqueDomain)
 
 
 class UnaryCoerce(Adapter):
         return DateDomain()
 
 
+class BinaryCoerceTime(BinaryCoerce):
+    """
+    Coerce to :class:`TimeDomain`.
+    """
+
+    adapts_many((TimeDomain, TimeDomain),
+                (TimeDomain, UntypedDomain),
+                (UntypedDomain, TimeDomain))
+
+    def __call__(self):
+        return TimeDomain()
+
+
+class BinaryCoerceDateTime(BinaryCoerce):
+    """
+    Coerce to :class:`DateTimeDomain`.
+    """
+
+    adapts_many((DateTimeDomain, DateTimeDomain),
+                (DateTimeDomain, UntypedDomain),
+                (UntypedDomain, DateTimeDomain))
+
+    def __call__(self):
+        return DateTimeDomain()
+
+
 class BinaryCoerceOpaque(BinaryCoerce):
     """
     Validate and coerce to :class:`OpaqueDomain`.

src/htsql/tr/dump.py

 from ..adapter import Adapter, Protocol, adapts, named
 from .error import SerializeError
 from ..domain import (Domain, BooleanDomain, IntegerDomain, DecimalDomain,
-                      FloatDomain, StringDomain, EnumDomain, DateDomain)
+                      FloatDomain, StringDomain, EnumDomain, DateDomain,
+                      TimeDomain, DateTimeDomain)
 from .syntax import IdentifierSyntax, CallSyntax, LiteralSyntax
 from .frame import (Clause, Frame, TableFrame, BranchFrame, NestedFrame,
                     SegmentFrame, QueryFrame,
         self.format("DATE {value:literal}", value=str(self.value))
 
 
+class DumpTime(DumpByDomain):
+    """
+    Serializes a time literal.
+    """
+
+    adapts(TimeDomain)
+
+    def __call__(self):
+        self.format("TIME {value:literal}", value=str(self.value))
+
+
+class DumpDateTime(DumpByDomain):
+    """
+    Serializes a datetime literal.
+    """
+
+    adapts(DateTimeDomain)
+
+    def __call__(self):
+        if self.value.tzinfo is None:
+            self.format("TIMESTAMP {value:literal}", value=str(self.value))
+        else:
+            self.format("TIMESTAMP WITH TIME ZONE {value:literal}",
+                        value=str(self.value))
+
+
 class DumpCast(Dump):
     """
     Serializes a ``CAST`` clause.
     """
     Serializes conversion to a date value.
 
-    Handles conversion from a string.
+    Handles conversion from a string and a datetime.
     """
 
     adapts(Domain, DateDomain)
         self.format("CAST({base} AS DATE)", base=self.base)
 
 
+class DumpToTime(DumpToDomain):
+    """
+    Serializes conversion to a time value.
+
+    Handles conversion from a string and a datetime.
+    """
+
+    adapts(Domain, TimeDomain)
+
+    def __call__(self):
+        self.format("CAST({base} AS TIME)", base=self.base)
+
+
+class DumpToDateTime(DumpToDomain):
+    """
+    Serializes conversion to a datetime value.
+
+    Handles conversion from a string.
+    """
+
+    adapts(Domain, DateTimeDomain)
+
+    def __call__(self):
+        self.format("CAST({base} AS TIMESTAMP)", base=self.base)
+
+
 class DumpFormula(Dump):
     """
     Serializes a formula node.

src/htsql/tr/encode.py

 from ..adapter import Adapter, adapts, adapts_many
 from ..domain import (Domain, UntypedDomain, TupleDomain, BooleanDomain,
                       NumberDomain, IntegerDomain, DecimalDomain, FloatDomain,
-                      StringDomain, EnumDomain, DateDomain, OpaqueDomain)
+                      StringDomain, EnumDomain, DateDomain, TimeDomain,
+                      DateTimeDomain, OpaqueDomain)
 from .error import EncodeError
 from .coerce import coerce
 from .binding import (Binding, RootBinding, QueryBinding, SegmentBinding,
                 (FloatDomain, FloatDomain),
                 (DecimalDomain, DecimalDomain),
                 (StringDomain, StringDomain),
-                (DateDomain, DateDomain))
+                (DateDomain, DateDomain),
+                (TimeDomain, TimeDomain),
+                (DateTimeDomain, DateTimeDomain))
     # FIXME: do we need `EnumDomain` here?
 
     def __call__(self):
     adapts_many((NumberDomain, BooleanDomain),
                 (EnumDomain, BooleanDomain),
                 (DateDomain, BooleanDomain),
+                (TimeDomain, BooleanDomain),
+                (DateTimeDomain, BooleanDomain),
                 (OpaqueDomain, BooleanDomain))
     # Note: we include the opaque domain here to ensure that any
     # data type could be converted to Boolean.  However this may
                 (NumberDomain, StringDomain),
                 (EnumDomain, StringDomain),
                 (DateDomain, StringDomain),
+                (TimeDomain, StringDomain),
+                (DateTimeDomain, StringDomain),
                 (OpaqueDomain, StringDomain))
     # Note: we assume we could convert any opaque data type to string;
     # it is risky but convenient.
     Convert an expression to a date value.
     """
 
-    adapts(StringDomain, DateDomain)
+    adapts_many((StringDomain, DateDomain),
+                (DateTimeDomain, DateDomain))
 
     def __call__(self):
         # We leave conversion from literal values to the database
                         self.binding)
 
 
+class ConvertToTime(Convert):
+    """
+    Convert an expression to a time value.
+    """
+
+    adapts_many((StringDomain, TimeDomain),
+                (DateTimeDomain, TimeDomain))
+
+    def __call__(self):
+        return CastCode(self.state.encode(self.base), self.domain,
+                        self.binding)
+
+
+class ConvertToDateTime(Convert):
+    """
+    Convert an expression to a datetime value.
+    """
+
+    adapts_many((StringDomain, DateTimeDomain),
+                (DateDomain, DateTimeDomain))
+
+    def __call__(self):
+        return CastCode(self.state.encode(self.base), self.domain,
+                        self.binding)
+
+
 class EncodeFormula(Encode):
     """
     Translates a formula binding to a code node.

src/htsql/tr/fn/bind.py

                          IsNullSig, IfNullSig, NullIfSig, AndSig, OrSig,
                          NotSig)
 from .signature import (FiberSig, AsSig, SortDirectionSig, LimitSig,
-                        SortSig, CastSig, MakeDateSig, ExtractYearSig,
-                        ExtractMonthSig, ExtractDaySig,
+                        SortSig, CastSig, MakeDateSig, MakeDateTimeSig,
+                        CombineDateTimeSig,
+                        ExtractYearSig, ExtractMonthSig, ExtractDaySig,
+                        ExtractHourSig, ExtractMinuteSig, ExtractSecondSig,
                         AddSig, ConcatenateSig,
                         HeadSig, TailSig, SliceSig, AtSig, ReplaceSig,
                         UpperSig, LowerSig, TrimSig,
-                        DateIncrementSig, SubtractSig, DateDecrementSig,
+                        DateIncrementSig, DateTimeIncrementSig,
+                        SubtractSig, DateDecrementSig, DateTimeDecrementSig,
                         DateDifferenceSig, TodaySig, NowSig,
                         MultiplySig, DivideSig, IfSig, SwitchSig,
                         KeepPolaritySig, ReversePolaritySig,
     hint = """date(expr) -> expression converted to date"""
 
 
+class BindTimeCast(BindCast):
+
+    named('time')
+    codomain = TimeDomain()
+
+
+class BindDateTimeCast(BindCast):
+
+    named('datetime')
+    codomain = DateTimeDomain()
+
+
 class BindMakeDate(BindMonoFunction):
 
     named(('date', 3))
     hint = """date(year, month, day) -> date value"""
 
 
+class BindMakeDateTime(BindMonoFunction):
+
+    named(('datetime', 3),
+          ('datetime', 4),
+          ('datetime', 5),
+          ('datetime', 6))
+    signature = MakeDateTimeSig
+    domains = [IntegerDomain(), IntegerDomain(), IntegerDomain(),
+               IntegerDomain(), IntegerDomain(), FloatDomain()]
+    codomain = DateTimeDomain()
+
+
+class BindCombineDateTime(BindMonoFunction):
+
+    named(('datetime', 2))
+    signature = CombineDateTimeSig
+    domains = [DateDomain(), TimeDomain()]
+    codomain = DateTimeDomain()
+
+
 class BindAmongBase(BindFunction):
 
     signature = IsInSig
 class ComparableDomains(Comparable):
 
     adapts_many(IntegerDomain, DecimalDomain, FloatDomain,
-                StringDomain, EnumDomain, DateDomain)
+                StringDomain, EnumDomain, DateDomain, TimeDomain,
+                DateTimeDomain)
 
     def __call__(self):
         return True
     codomain = DateDomain()
 
 
+class CorrelateDateTimeIncrement(CorrelateFunction):
+
+    correlates(AddSig, (DateTimeDomain, IntegerDomain),
+                       (DateTimeDomain, DecimalDomain),
+                       (DateTimeDomain, FloatDomain))
+    signature = DateTimeIncrementSig
+    domains = [DateTimeDomain(), FloatDomain()]
+    codomain = DateTimeDomain()
+
+
 class CorrelateConcatenate(CorrelateFunction):
 
     correlates(AddSig, (UntypedDomain, UntypedDomain),
     codomain = DateDomain()
 
 
+class CorrelateDateTimeDecrement(CorrelateFunction):
+
+    correlates(SubtractSig, (DateTimeDomain, IntegerDomain),
+                            (DateTimeDomain, DecimalDomain),
+                            (DateTimeDomain, FloatDomain))
+    signature = DateTimeDecrementSig
+    domains = [DateTimeDomain(), FloatDomain()]
+    codomain = DateTimeDomain()
+
+
 class CorrelateDateDifference(CorrelateFunction):
 
     correlates(SubtractSig, (DateDomain, DateDomain))
     hint = """day(date) -> the day of a given date"""
 
 
-class CorrelateExtractYear(CorrelateFunction):
+class BindExtractHour(BindPolyFunction):
+
+    named('hour')
+    signature = ExtractHourSig
+
+
+class BindExtractMinute(BindPolyFunction):
+
+    named('minute')
+    signature = ExtractMinuteSig
+
+
+class BindExtractSecond(BindPolyFunction):
+
+    named('second')
+    signature = ExtractSecondSig
+
+
+class CorrelateExtractYearFromDate(CorrelateFunction):
 
     correlates(ExtractYearSig, DateDomain)
     domains = [DateDomain()]
     codomain = IntegerDomain()
 
 
-class CorrelateExtractMonth(CorrelateFunction):
+class CorrelateExtractYearFromDateTime(CorrelateFunction):
+
+    correlates(ExtractYearSig, DateTimeDomain)
+    domains = [DateTimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractMonthFromDate(CorrelateFunction):
 
     correlates(ExtractMonthSig, DateDomain)
     domains = [DateDomain()]
     codomain = IntegerDomain()
 
 
-class CorrelateExtractDay(CorrelateFunction):
+class CorrelateExtractMonthFromDateTime(CorrelateFunction):
+
+    correlates(ExtractMonthSig, DateTimeDomain)
+    domains = [DateTimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractDayFromDate(CorrelateFunction):
 
     correlates(ExtractDaySig, DateDomain)
     domains = [DateDomain()]
     codomain = IntegerDomain()
 
 
+class CorrelateExtractDayFromDateTime(CorrelateFunction):
+
+    correlates(ExtractDaySig, DateTimeDomain)
+    domains = [DateTimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractHourFromTime(CorrelateFunction):
+
+    correlates(ExtractHourSig, TimeDomain)
+    domains = [TimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractHourFromDateTime(CorrelateFunction):
+
+    correlates(ExtractHourSig, DateTimeDomain)
+    domains = [DateTimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractMinuteFromTime(CorrelateFunction):
+
+    correlates(ExtractMinuteSig, TimeDomain)
+    domains = [TimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractMinuteFromDateTime(CorrelateFunction):
+
+    correlates(ExtractMinuteSig, DateTimeDomain)
+    domains = [DateTimeDomain()]
+    codomain = IntegerDomain()
+
+
+class CorrelateExtractSecondFromTime(CorrelateFunction):
+
+    correlates(ExtractSecondSig, TimeDomain)
+    domains = [TimeDomain()]
+    codomain = FloatDomain()
+
+
+class CorrelateExtractSecondFromDateTime(CorrelateFunction):
+
+    correlates(ExtractSecondSig, DateTimeDomain)
+    domains = [DateTimeDomain()]
+    codomain = FloatDomain()
+
+
 class CorrelateTrim(CorrelateFunction):
 
     correlates(TrimSig, UntypedDomain,
     codomain = DateDomain()
 
 
+class CorrelateTimeMinMax(CorrelateAggregate):
+
+    correlates(MinMaxSig, TimeDomain)
+    signature = MinMaxSig
+    domains = [TimeDomain()]
+    codomain = TimeDomain()
+
+
+class CorrelateDateTimeMinMax(CorrelateAggregate):
+
+    correlates(MinMaxSig, DateTimeDomain)
+    signature = MinMaxSig
+    domains = [DateTimeDomain()]
+    codomain = DateTimeDomain()
+
+
 class BindSum(BindPolyAggregate):
 
     named('sum')

src/htsql/tr/fn/dump.py

 from ...adapter import adapts, adapts_none
 from ..dump import DumpBySignature
 from .signature import (AddSig, ConcatenateSig, DateIncrementSig,
-                        SubtractSig, DateDecrementSig, DateDifferenceSig,
+                        DateTimeIncrementSig, SubtractSig, DateDecrementSig,
+                        DateTimeDecrementSig, DateDifferenceSig,
                         MultiplySig, DivideSig, IfSig, SwitchSig,
                         ReversePolaritySig, RoundSig, RoundToSig,
                         LengthSig, LikeSig, ReplaceSig, SubstringSig,
                         UpperSig, LowerSig, TrimSig, TodaySig, NowSig,
-                        MakeDateSig, ExtractYearSig, ExtractMonthSig,
-                        ExtractDaySig, ExistsSig, CountSig, MinMaxSig,
-                        SumSig, AvgSig)
+                        MakeDateSig, MakeDateTimeSig, CombineDateTimeSig,
+                        ExtractYearSig, ExtractMonthSig, ExtractDaySig,
+                        ExtractHourSig, ExtractMinuteSig, ExtractSecondSig,
+                        ExistsSig, CountSig, MinMaxSig, SumSig, AvgSig)
 
 
 class DumpFunction(DumpBySignature):
     template = "CAST({lop} + {rop} * INTERVAL '1' DAY AS DATE)"
 
 
+class DumpDateTimeIncrement(DumpFunction):
+
+    adapts(DateTimeIncrementSig)
+    template = "({lop} + {rop} * INTERVAL '1' DAY)"
+
+
 class DumpDateDecrement(DumpFunction):
 
     adapts(DateDecrementSig)
     template = "CAST({lop} - {rop} * INTERVAL '1' DAY AS DATE)"
 
 
+class DumpDateTimeDecrement(DumpFunction):
+
+    adapts(DateTimeDecrementSig)
+    template = "({lop} - {rop} * INTERVAL '1' DAY)"
+
+
 class DumpDateDifference(DumpFunction):
 
     adapts(DateDifferenceSig)
                 " + ({day} - 1) * INTERVAL '1' DAY AS DATE)")
 
 
+class DumpMakeDateTime(DumpFunction):
+
+    adapts(MakeDateTimeSig)
+
+    def __call__(self):
+        template = ("(TIMESTAMP '2001-01-01 00:00:00'"
+                    " + ({year} - 2001) * INTERVAL '1' YEAR"
+                    " + ({month} - 1) * INTERVAL '1' MONTH"
+                    " + ({day} - 1) * INTERVAL '1' DAY")
+        if self.phrase.hour is not None:
+            template += " + {hour} * INTERVAL '1' HOUR"
+        if self.phrase.minute is not None:
+            template += " + {minute} * INTERVAL '1' MINUTE"
+        if self.phrase.second is not None:
+            template += " + {second} * INTERVAL '1' SECOND"
+        template += ")"
+        self.format(template, self.arguments)
+
+
+class DumpCombineDateTime(DumpFunction):
+
+    adapts(CombineDateTimeSig)
+    template = "({date} + {time})"
+
+
 class DumpExtractYear(DumpFunction):
 
     adapts(ExtractYearSig)
     template = "EXTRACT(DAY FROM {op})"
 
 
+class DumpExtractHour(DumpFunction):
+
+    adapts(ExtractHourSig)
+    template = "EXTRACT(HOUR FROM {op})"
+
+
+class DumpExtractMinute(DumpFunction):
+
+    adapts(ExtractMinuteSig)
+    template = "EXTRACT(MINUTE FROM {op})"
+
+
+class DumpExtractSecond(DumpFunction):
+
+    adapts(ExtractSecondSig)
+    template = "EXTRACT(SECOND FROM {op})"
+
+
 class DumpExists(DumpFunction):
 
     adapts(ExistsSig)

src/htsql/tr/fn/signature.py

     ]
 
 
+class MakeDateTimeSig(Signature):
+
+    slots = [
+            Slot('year'),
+            Slot('month'),
+            Slot('day'),
+            Slot('hour', is_mandatory=False),
+            Slot('minute', is_mandatory=False),
+            Slot('second', is_mandatory=False),
+    ]
+
+
+class CombineDateTimeSig(Signature):
+
+    slots = [
+            Slot('date'),
+            Slot('time'),
+    ]
+
+
 class ExtractSig(UnarySig):
     pass
 
     pass
 
 
+class ExtractHourSig(ExtractSig):
+    pass
+
+
+class ExtractMinuteSig(ExtractSig):
+    pass
+
+
+class ExtractSecondSig(ExtractSig):
+    pass
+
+
 class AddSig(BinarySig):
     pass
 
     pass
 
 
+class DateTimeIncrementSig(AddSig):
+    pass
+
+
 class SubtractSig(BinarySig):
     pass
 
     pass
 
 
+class DateTimeDecrementSig(SubtractSig):
+    pass
+
+
 class DateDifferenceSig(SubtractSig):
     pass
 

src/htsql_mssql/connect.py

 from htsql.connect import Connect, Normalize, DBError
 from htsql.adapter import adapts
 from htsql.context import context
-from htsql.domain import BooleanDomain, StringDomain, DateDomain
+from htsql.domain import BooleanDomain, StringDomain, DateDomain, TimeDomain
 import datetime
 import pymssql
 
 
     def __call__(self, value):
         if isinstance(value, datetime.datetime):
+            assert not value.time()
             value = value.date()
         return value
 
 
+class NormalizeMSSQLTime(Normalize):
+
+    adapts(TimeDomain)
+
+    def __call__(self, value):
+        if isinstance(value, float):
+            assert 0.0 <= value < 1.0
+            value = int(86400000000*value) * datetime.timedelta(0,0,1)
+            assert not value.days
+            value = (datetime.datetime(2001,1,1) + value).time()
+        return value
+
+

src/htsql_mssql/introspect.py

                           ForeignKeyEntity)
 from .domain import (MSSQLBooleanDomain, MSSQLIntegerDomain,
                      MSSQLDecimalDomain, MSSQLFloatDomain, MSSQLStringDomain,
-                     MSSQLDateDomain, MSSQLOpaqueDomain)
+                     MSSQLDateTimeDomain, MSSQLOpaqueDomain)
 from htsql.connect import Connect
 from htsql.util import Record
 
             return MSSQLFloatDomain(schema_name, type_name,
                                     size=max_length*8)
         elif name in [('sys', 'datetime'), ('sys', 'smalldatetime')]:
-            return MSSQLDateDomain(schema_name, type_name)
+            return MSSQLDateTimeDomain(schema_name, type_name)
         return MSSQLOpaqueDomain(schema_name, type_name)
 
 

src/htsql_mssql/tr/dump.py

 
 from htsql.adapter import adapts
 from htsql.domain import (BooleanDomain, StringDomain, IntegerDomain,
-                          DecimalDomain, DateDomain)
+                          DecimalDomain, DateDomain, TimeDomain, DateTimeDomain)
 from htsql.tr.error import SerializeError
 from htsql.tr.frame import ColumnPhrase, ReferencePhrase, LiteralPhrase
 from htsql.tr.dump import (FormatName, DumpBranch, DumpBySignature,
                            DumpFromPredicate, DumpToPredicate,
                            DumpIsTotallyEqual, DumpBoolean, DumpInteger,
-                           DumpDecimal, DumpFloat, DumpDate, DumpToInteger,
+                           DumpDecimal, DumpFloat, DumpDate, DumpTime,
+                           DumpDateTime, DumpToInteger,
                            DumpToFloat, DumpToDecimal, DumpToString,
-                           DumpToDate)
+                           DumpToDate, DumpToTime, DumpToDateTime)
 from htsql.tr.fn.dump import (DumpRound, DumpRoundTo, DumpLength,
                               DumpConcatenate, DumpSubstring, DumpTrim,
                               DumpToday, DumpNow, DumpExtractYear,
-                              DumpExtractMonth, DumpExtractDay, DumpMakeDate,
+                              DumpExtractMonth, DumpExtractDay,
+                              DumpExtractHour, DumpExtractMinute,
+                              DumpExtractSecond, DumpMakeDate, DumpMakeDateTime,
                               DumpDateIncrement, DumpDateDecrement,
+                              DumpDateTimeIncrement, DumpDateTimeDecrement,
                               DumpDateDifference)
 from htsql.tr.signature import FromPredicateSig, ToPredicateSig
 from htsql.tr.fn.signature import SortDirectionSig
                     value=str(self.value))
 
 
+class MSSQLDumpTime(DumpTime):
+
+    def __call__(self):
+        value = (self.value.hour*3600 + self.value.minute*60 +
+                 self.value.second + self.value.microsecond/1000000.0) / 86400.0
+        value = repr(value)
+        if 'e' not in value and 'E' not in value:
+            value = value+'e0'
+        self.write(value)
+
+
+class MSSQLDumpDateTime(DumpDateTime):
+
+    def __call__(self):
+        value = self.value.replace(tzinfo=None)
+        if not value.microsecond:
+            value = str(value)
+        else:
+            value = str(value)[:-3]
+        self.format("CAST({value:literal} AS DATETIME)", value=value)
+
+
 class MSSQLDumpToInteger(DumpToInteger):
 
     def __call__(self):
     adapts(DateDomain, StringDomain)
 
     def __call__(self):
-        self.format("SUBSTRING(CONVERT(VARCHAR, {base}, 20), 1, 10)",
+        self.format("SUBSTRING(CONVERT(VARCHAR, {base}, 21), 1, 10)",
                     base=self.base)
 
 
-class MSSQLDumpToDate(DumpToDate):
+class MSSQLDumpTimeToString(DumpToString):
+
+    adapts(TimeDomain, StringDomain)
+
+    def __call__(self):
+        self.format("SUBSTRING(CONVERT(VARCHAR, CAST({base} AS DATETIME), 21),"
+                    " 12, 12)", base=self.base)
+
+
+class MSSQLDumpDateTimeToString(DumpToString):
+
+    adapts(DateTimeDomain, StringDomain)
+
+    def __call__(self):
+        self.format("CONVERT(VARCHAR, {base}, 21)", base=self.base)
+
+
+class MSSQLDumpStringToDate(DumpToDate):
+
+    adapts(StringDomain, DateDomain)
+
+    def __call__(self):
+        self.format("CAST(FLOOR(CAST(CAST({base} AS DATETIME) AS FLOAT))"
+                    " AS DATETIME)", base=self.base)
+
+
+class MSSQLDumpDateTimeToDate(DumpToDate):
+
+    adapts(DateTimeDomain, DateDomain)
+
+    def __call__(self):
+        self.format("CAST(FLOOR(CAST({base} AS FLOAT)) AS DATETIME)",
+                    base=self.base)
+
+
+class MSSQLDumpStringToTime(DumpToTime):
+
+    adapts(StringDomain, TimeDomain)
+
+    def __call__(self):
+        self.format("CAST(CAST('1900-01-01 ' + {base} AS DATETIME) AS FLOAT)",
+                    base=self.base)
+
+
+class MSSQLDumpDateTimeToTime(DumpToTime):
+
+    adapts(DateTimeDomain, TimeDomain)
+
+    def __call__(self):
+        self.format("(CAST({base} AS FLOAT) - FLOOR(CAST({base} AS FLOAT)))",
+                    base=self.base)
+
+
+class MSSQLDumpStringToDateTime(DumpToDateTime):
+
+    adapts(StringDomain, DateTimeDomain)
 
     def __call__(self):
         self.format("CAST({base} AS DATETIME)", base=self.base)
 
 
+class MSSQLDumpDateToDateTime(DumpToDateTime):
+
+    adapts(DateDomain, DateTimeDomain)
+
+    def __call__(self):
+        self.format("{base}", base=self.base)
+
+
 class MSSQLDumpIsTotallyEqual(DumpIsTotallyEqual):
 
     def __call__(self):
     template = "DATEPART(DAY, {op})"
 
 
+class MSSQLDumpExtractHour(DumpExtractHour):
+
+    template = "DATEPART(HOUR, {op})"
+
+
+class MSSQLDumpExtractMinute(DumpExtractMinute):
+
+    template = "DATEPART(MINUTE, {op})"
+
+
+class MSSQLDumpExtractSecond(DumpExtractSecond):
+
+    template = ("(DATEPART(SECOND, {op}) +"
+                " DATEPART(MILLISECOND, {op}) / 1000e0)")
+
+
 class MSSQLDumpMakeDate(DumpMakeDate):
 
     template = ("DATEADD(DAY, {day} - 1,"
                 " CAST('2001-01-01' AS DATETIME))))")
 
 
+class MSSQLDumpMakeDateTime(DumpMakeDateTime):
+
+    def __call__(self):
+        template = ("DATEADD(DAY, {day} - 1,"
+                    " DATEADD(MONTH, {month} - 1,"
+                    " DATEADD(YEAR, {year} - 2001,"
+                    " CAST('2001-01-01' AS DATETIME))))")
+        if self.phrase.hour is not None:
+            template = "DATEADD(HOUR, {hour}, %s)" % template
+        if self.phrase.minute is not None:
+            template = "DATEADD(MINUTE, {minute}, %s)" % template
+        if self.phrase.second is not None:
+            template = "DATEADD(MILLISECOND, 1000 * {second}, %s)" % template
+        self.format(template, self.arguments)
+
+
 class MSSQLDumpDateIncrement(DumpDateIncrement):
 
-    template = "DATEADD(DAY, {rop}, {lop})"
+    template = "({lop} + {rop})"
+
+
+class MSSQLDumpDateTimeIncrement(DumpDateTimeIncrement):
+
+    template = "({lop} + {rop})"
 
 
 class MSSQLDumpDateDecrement(DumpDateDecrement):
 
-    template = "DATEADD(DAY, -{rop}, {lop})"
+    template = "({lop} - {rop})"
+
+
+class MSSQLDumpDateTimeDecrement(DumpDateTimeDecrement):
+
+    template = "({lop} - {rop})"
 
 
 class MSSQLDumpDateDifference(DumpDateDifference):

src/htsql_mysql/connect.py

 from htsql.connect import Connect, Normalize, DBError
 from htsql.adapter import adapts
 from htsql.context import context
-from htsql.domain import BooleanDomain, StringDomain, EnumDomain
+from htsql.domain import BooleanDomain, StringDomain, EnumDomain, TimeDomain
 import MySQLdb, MySQLdb.connections
+import datetime
 
 
 class Cursor(MySQLdb.connections.Connection.default_cursor):
         return value
 
 
+class NormalizeMySQLTime(Normalize):
 
+    adapts(TimeDomain)
+
+    def __call__(self, value):
+        if isinstance(value, datetime.timedelta):
+            if value.days != 0:
+                value = None
+            else:
+                value = (datetime.datetime(2001,1,1) + value).time()
+        return value
+
+

src/htsql_mysql/introspect.py

                           ForeignKeyEntity)
 from .domain import (MySQLBooleanDomain, MySQLIntegerDomain,
                      MySQLDecimalDomain, MySQLFloatDomain, MySQLStringDomain,
-                     MySQLEnumDomain, MySQLDateDomain, MySQLOpaqueDomain)
+                     MySQLEnumDomain, MySQLDateDomain, MySQLTimeDomain,
+                     MySQLDateTimeDomain, MySQLOpaqueDomain)
 from htsql.connect import Connect
 from htsql.util import Record
 
             return MySQLFloatDomain(data_type)
         elif data_type == 'date':
             return MySQLDateDomain(data_type)
+        elif data_type == 'time':
+            return MySQLTimeDomain(data_type)
+        elif data_type in ['datetime', 'timestamp']:
+            return MySQLDateTimeDomain(data_type)
         return MySQLOpaqueDomain(data_type)
 
 

src/htsql_mysql/tr/dump.py

                           StringDomain)
 from htsql.tr.dump import (FormatName, FormatLiteral,
                            DumpBoolean, DumpDecimal, DumpFloat, DumpDate,
+                           DumpTime, DumpDateTime,
                            DumpToDomain, DumpToInteger, DumpToFloat,
-                           DumpToDecimal, DumpToString, DumpIsTotallyEqual)
+                           DumpToDecimal, DumpToString, DumpToDateTime,
+                           DumpIsTotallyEqual)
 from htsql.tr.fn.dump import (DumpLength, DumpSubstring, DumpTrim,
-                              DumpDateIncrement, DumpDateDecrement,
-                              DumpDateDifference, DumpConcatenate, DumpLike,
-                              DumpMakeDate, DumpSum)
+                              DumpDateIncrement, DumpDateTimeIncrement,
+                              DumpDateDecrement, DumpDateTimeDecrement,
+                              DumpDateDifference, DumpExtractSecond,
+                              DumpConcatenate, DumpLike,
+                              DumpMakeDate, DumpMakeDateTime,
+                              DumpCombineDateTime, DumpSum)
 
 
 class MySQLFormatName(FormatName):
         self.format("DATE({value:literal})", value=str(self.value))
 
 
+class MySQLDumpTime(DumpTime):
+
+    def __call__(self):
+        self.format("TIME({value:literal})", value=str(self.value))
+
+
+class MySQLDumpDateTime(DumpDateTime):
+
+    def __call__(self):
+        # MySQLdb driver does not handle datetime values with microseconds.
+        value = self.value.replace(microsecond=0, tzinfo=None)
+        self.format("TIMESTAMP({value:literal})", value=str(value))
+
+
 class MySQLDumpToInteger(DumpToInteger):
 
     def __call__(self):
         self.format("CAST({base} AS CHAR)", base=self.base)
 
 
+class MySQLDumpToDateTime(DumpToDateTime):
+
+    def __call__(self):
+        self.format("CAST({base} AS DATETIME)", base=self.base)
+
+
 class MySQLDumpBooleanToString(DumpToDomain):
 
     adapts(BooleanDomain, StringDomain)
     template = "ADDDATE({lop}, {rop})"
 
 
+class MySQLDumpDateTimeIncrement(DumpDateTimeIncrement):
+
+    template = "ADDDATE({lop}, INTERVAL 86400 * {rop} SECOND)"
+
+
 class MySQLDumpDateDecrement(DumpDateDecrement):
 
     template = "SUBDATE({lop}, {rop})"
 
 
+class MySQLDumpDateTimeDecrement(DumpDateTimeDecrement):
+
+    template = "SUBDATE({lop}, INTERVAL 86400 * {rop} SECOND)"
+
+
 class MySQLDumpDateDifference(DumpDateDifference):
 
     template = "DATEDIFF({lop}, {rop})"
 
 
+class MySQLDumpExtractSecond(DumpExtractSecond):
+
+    template = "(1E0 * EXTRACT(SECOND FROM {op}))"
+
+
 class MySQLDumpConcatenate(DumpConcatenate):
 
     template = "CONCAT({lop}, {rop})"
                 " INTERVAL ({day} - 1) DAY)")
 
 
+class MySQLDumpMakeDateTime(DumpMakeDateTime):
+
+    def __call__(self):
+        template = ("ADDDATE(ADDDATE(ADDDATE(TIMESTAMP('2001-01-01'),"
+                    " INTERVAL ({year} - 2001) YEAR),"
+                    " INTERVAL ({month} - 1) MONTH),"
+                    " INTERVAL ({day} - 1) DAY)")
+        if self.phrase.hour is not None:
+            template = "ADDDATE(%s, INTERVAL {hour} HOUR)" % template
+        if self.phrase.minute is not None:
+            template = "ADDDATE(%s, INTERVAL {minute} MINUTE)" % template
+        if self.phrase.second is not None:
+            template = "ADDDATE(%s, INTERVAL {second} SECOND)" % template
+        self.format(template, self.arguments)
+
+
+class MySQLDumpCombineDateTime(DumpCombineDateTime):
+
+    template = "ADDTIME(TIMESTAMP({date}), {time})"
+
+
 class MySQLDumpSum(DumpSum):
 
     def __call__(self):

src/htsql_oracle/connect.py

 from htsql.connect import Connect, Normalize, DBError
 from htsql.adapter import adapts
 from htsql.context import context
-from htsql.domain import BooleanDomain, DecimalDomain, StringDomain, DateDomain
+from htsql.domain import (BooleanDomain, DecimalDomain, StringDomain,
+                          DateDomain, TimeDomain)
 import datetime
 import decimal
 import cx_Oracle
 
     def __call__(self, value):
         if isinstance(value, datetime.datetime):
+            assert not value.time()
             value = value.date()
         return value
 
 
+class NormalizeOracleTime(Normalize):
+
+    adapts(TimeDomain)
+
+    def __call__(self, value):
+        if isinstance(value, datetime.timedelta):
+            assert not value.days
+            value = (datetime.datetime(2001,1,1) + value).time()
+        return value
+
+

src/htsql_oracle/introspect.py

                           ForeignKeyEntity)
 from .domain import (OracleBooleanDomain, OracleIntegerDomain,
                      OracleDecimalDomain, OracleFloatDomain,
-                     OracleStringDomain, OracleDateDomain,
+                     OracleStringDomain, OracleDateTimeDomain,
                      OracleOpaqueDomain)
 from htsql.connect import Connect
 from htsql.util import Record
                                        scale=data_scale)
         elif data_type in ['BINARY_FLOAT', 'BINARY_DOUBLE']:
             return OracleFloatDomain(data_type)
-        elif data_type == 'DATE':
-            return OracleDateDomain(data_type)
+        elif data_type == 'DATE' or data_type.startswith('TIMESTAMP'):
+            return OracleDateTimeDomain(data_type)
         return OracleOpaqueDomain(data_type)
 
 

src/htsql_oracle/tr/dump.py

 
 
 from htsql.adapter import adapts
-from htsql.domain import BooleanDomain, StringDomain, DateDomain
+from htsql.domain import (BooleanDomain, StringDomain, DateDomain, TimeDomain,
+                          DateTimeDomain)
 from htsql.tr.frame import ScalarFrame
 from htsql.tr.dump import (SerializeSegment, Dump, DumpBranch, DumpAnchor,
                            DumpLeadingAnchor, DumpFromPredicate,
                            DumpToPredicate, DumpBoolean, DumpInteger,
-                           DumpFloat, DumpToFloat, DumpToDecimal,
-                           DumpToString,
+                           DumpFloat, DumpTime, DumpDateTime,
+                           DumpToFloat, DumpToDecimal, DumpToString,
+                           DumpToDate, DumpToTime, DumpToDateTime,
                            DumpIsTotallyEqual, DumpBySignature)
 from htsql.tr.fn.dump import (DumpLength, DumpSubstring, DumpDateIncrement,
                               DumpDateDecrement, DumpDateDifference,
-                              DumpMakeDate)
+                              DumpMakeDate, DumpCombineDateTime,
+                              DumpExtractSecond)
 from .signature import RowNumSig
 
 
         self.write(repr(self.value)+'D')
 
 
+class OracleDumpTime(DumpTime):
+
+    def __call__(self):
+        self.format("INTERVAL {value:literal} HOUR TO SECOND", value=str(self.value))
+
+
+class OracleDumpDateTime(DumpDateTime):
+
+    def __call__(self):
+        self.format("TIMESTAMP {value:literal}", value=str(self.value))
+
+
 class OracleDumpToFloat(DumpToFloat):
 
     def __call__(self):
         self.format("TO_CHAR({base}, 'YYYY-MM-DD')", base=self.base)
 
 
+class OracleDumpTimeToString(DumpToString):
+
+    adapts(TimeDomain, StringDomain)
+
+    def __call__(self):
+        self.format("TO_CHAR(TIMESTAMP '2001-01-01 00:00:00' + {base},"
+                    " 'HH24:MI:SS.FF')", base=self.base)
+
+
+class OracleDumpDateTimeToString(DumpToString):
+
+    adapts(DateTimeDomain, StringDomain)
+
+    def __call__(self):
+        self.format("TO_CHAR({base}, 'YYYY-MM-DD HH24:MI:SS.FF')",
+                    base=self.base)
+
+
+class OracleDumpStringToDate(DumpToDate):
+
+    adapts(StringDomain, DateDomain)
+
+    def __call__(self):
+        self.format("TO_DATE({base}, 'YYYY-MM-DD')", base=self.base)
+
+
+class OracleDumpDateTimeToDate(DumpToDate):
+
+    adapts(DateTimeDomain, DateDomain)
+
+    def __call__(self):
+        self.format("TRUNC({base}, 'DD')", base=self.base)
+
+
+class OracleDumpStringToTime(DumpToTime):
+
+    adapts(StringDomain, TimeDomain)
+
+    def __call__(self):
+        self.format("TO_DSINTERVAL('0 ' || {base})", base=self.base)
+
+
+class OracleDumpDateTimeToTime(DumpToTime):
+
+    adapts(DateTimeDomain, TimeDomain)
+
+    def __call__(self):
+        self.format("({base} - TRUNC({base}, 'DD'))", base=self.base)
+
+
+class OracleDumpStringToDateTime(DumpToDateTime):
+
+    adapts(StringDomain, DateTimeDomain)
+
+    def __call__(self):
+        self.format("TO_TIMESTAMP({base}, 'YYYY-MM-DD HH24:MI:SS')",
+                    base=self.base)
+
+
 class OracleDumpIsTotallyEqual(DumpIsTotallyEqual):
 
     def __call__(self):
                 " + ({day} - 1) * INTERVAL '1' DAY)")
 
 
+class OracleDumpCombineDateTime(DumpCombineDateTime):
+
+    template = "(CAST({date} AS TIMESTAMP) + {time})"
+
+
+class OracleDumpExtractSecond(DumpExtractSecond):
+
+    template = "(1D * EXTRACT(SECOND FROM {op}))"
+
+

src/htsql_pgsql/introspect.py

 from .domain import (PGBooleanDomain, PGIntegerDomain, PGFloatDomain,
                      PGDecimalDomain, PGCharDomain, PGVarCharDomain,
                      PGTextDomain, PGEnumDomain, PGDateDomain,
-                     PGOpaqueDomain)
+                     PGTimeDomain, PGDateTimeDomain, PGOpaqueDomain)
 import rulesparser
 from htsql.connect import Connect
 from htsql.util import Record
                 return PGTextDomain(schema_name, name)
             if base_name == 'date':
                 return PGDateDomain(schema_name, name)
+            if base_name in ['time', 'timetz']:
+                return PGTimeDomain(schema_name, name)
+            if base_name in ['timestamp', 'timestamptz']:
+                return PGDateTimeDomain(schema_name, name)
         return PGOpaqueDomain(schema_name, name)
 
 

src/htsql_pgsql/tr/dump.py

 
 
 from htsql.tr.dump import (FormatLiteral, DumpBranch, DumpFloat,
-                           DumpDecimal, DumpDate, DumpToDecimal, DumpToFloat,
-                           DumpToString)
+                           DumpDecimal, DumpDate, DumpTime, DumpDateTime,
+                           DumpToDecimal, DumpToFloat, DumpToString)
 from htsql.tr.fn.dump import (DumpLike, DumpDateIncrement,
                               DumpDateDecrement, DumpDateDifference,
                               DumpMakeDate, DumpExtractYear, DumpExtractMonth,
-                              DumpExtractDay)
+                              DumpExtractDay, DumpExtractHour,
+                              DumpExtractMinute)
 
 
 class PGSQLFormatLiteral(FormatLiteral):
         self.format("{value:literal}::DATE", value=str(self.value))
 
 
+class PGSQLDumpTime(DumpTime):
+
+    def __call__(self):
+        self.format("{value:literal}::TIME", value=str(self.value))
+
+
+class PGSQLDumpDateTime(DumpDateTime):
+
+    def __call__(self):
+        if self.value.tzinfo is None:
+            self.format("{value:literal}::TIMESTAMP", value=str(self.value))
+        else:
+            self.format("{value:literal}::TIMESTAMPTZ", value=str(self.value))
+
+
 class PGSQLDumpToFloat(DumpToFloat):
 
     def __call__(self):
     template = "CAST(EXTRACT(DAY FROM {op}) AS INTEGER)"
 
 
+class PGSQLDumpExtractHour(DumpExtractHour):
+
+    template = "CAST(EXTRACT(HOUR FROM {op}) AS INTEGER)"
+
+
+class PGSQLDumpExtractMinute(DumpExtractMinute):
+
+    template = "CAST(EXTRACT(MINUTE FROM {op}) AS INTEGER)"
+
+
 class PGSQLDumpLike(DumpLike):
 
     def __call__(self):

src/htsql_sqlite/connect.py

 from htsql.connect import Connect, Normalize, DBError
 from htsql.adapter import adapts
 from htsql.context import context
-from htsql.domain import BooleanDomain, StringDomain, DateDomain
+from htsql.domain import (BooleanDomain, StringDomain, DateDomain, TimeDomain,
+                          DateTimeDomain)
 import sqlite3
+import datetime
 
 
 class SQLiteError(DBError):
         return value
 
 
+class NormalizeSQLiteTime(Normalize):
+
+    adapts(TimeDomain)
+
+    def __call__(self, value):
+        if isinstance(value, (str, unicode)):
+            hour, minute, second = value.split(':')
+            hour = int(hour)
+            minute = int(minute)
+            if '.' in second:
+                second, microsecond = second.split('.')
+                second = int(second)
+                microsecond = int(microsecond)
+            else:
+                second = int(second)
+                microsecond = 0
+            value = datetime.time(hour, minute, second, microsecond)
+        return value
+
+
+class NormalizeSQLiteDateTime(Normalize):
+
+    adapts(DateTimeDomain)
+
+    def __call__(self, value):
+        if isinstance(value, (str, unicode)):
+            converter = sqlite3.converters['TIMESTAMP']
+            value = converter(value)
+        return value
+
+

src/htsql_sqlite/introspect.py

                           ForeignKeyEntity)
 from .domain import (SQLiteBooleanDomain, SQLiteIntegerDomain,
                      SQLiteFloatDomain, SQLiteTextDomain, SQLiteDateDomain,
-                     SQLiteOpaqueDomain)
+                     SQLiteDateTimeDomain, SQLiteOpaqueDomain)
 from htsql.connect import Connect
 from htsql.util import Record
 
             return SQLiteFloatDomain(name)
         if 'bool' in type_name:
             return SQLiteBooleanDomain(name)
+        if 'datetime' in type_name or 'timestamp' in type_name:
+            return SQLiteDateTimeDomain(name)
         if 'date' in type_name:
             return SQLiteDateDomain(name)
         return SQLiteOpaqueDomain(name)

src/htsql_sqlite/tr/dump.py

 from htsql.adapter import adapts
 from htsql.domain import BooleanDomain, StringDomain
 from htsql.tr.dump import (DumpTable, DumpBoolean, DumpDecimal, DumpDate,
+                           DumpTime, DumpDateTime,
                            DumpToFloat, DumpToDecimal, DumpToString,
-                           DumpToDate, DumpIsTotallyEqual)
+                           DumpToDate, DumpToTime, DumpToDateTime,
+                           DumpIsTotallyEqual)
 from htsql.tr.fn.dump import (DumpLength, DumpSubstring, DumpTrim,
-                              DumpDateIncrement, DumpDateDecrement,
+                              DumpDateIncrement, DumpDateTimeIncrement,
+                              DumpDateDecrement, DumpDateTimeDecrement,
                               DumpDateDifference, DumpMakeDate,
+                              DumpMakeDateTime, DumpCombineDateTime,
                               DumpExtractYear, DumpExtractMonth,
-                              DumpExtractDay, DumpToday, DumpNow)
+                              DumpExtractDay, DumpExtractHour,
+                              DumpExtractMinute, DumpExtractSecond,
+                              DumpToday, DumpNow)
 from htsql.tr.error import SerializeError
 
 
         self.format("{value:literal}", value=str(self.value))
 
 
+class SQLiteDumpTime(DumpTime):
+
+    def __call__(self):
+        value = self.value.replace(tzinfo=None)
+        self.format("{value:literal}", value=str(value))
+
+
+class SQLiteDumpDateTime(DumpDateTime):
+
+    def __call__(self):
+        value = self.value.replace(tzinfo=None)
+        self.format("{value:literal}", value=str(value))
+
+
 class SQLiteDumpToFloat(DumpToFloat):
 
     def __call__(self):
         self.format("DATE({base})", base=self.base)
 
 
+class SQLiteDumpToTime(DumpToTime):
+
+    def __call__(self):
+        self.format("TIME({base})", base=self.base)
+
+
+class SQLiteDumpToDateTime(DumpToDateTime):
+
+    def __call__(self):
+        self.format("DATETIME({base})", base=self.base)
+
+
 class SQLiteDumpIsTotallyEqual(DumpIsTotallyEqual):
 
     def __call__(self):
     template = "DATE(JULIANDAY({lop}) + {rop})"
 
 
+class SQLiteDumpDateTimeIncrement(DumpDateTimeIncrement):
+
+    template = "DATETIME(JULIANDAY({lop}) + {rop})"
+
+
 class SQLiteDumpDateDecrement(DumpDateDecrement):
 
     template = "DATE(JULIANDAY({lop}) - {rop})"
 
 
+class SQLiteDumpDateTimeDecrement(DumpDateTimeDecrement):
+
+    template = "DATETIME(JULIANDAY({lop}) - {rop})"
+
+
 class SQLiteDumpDateDifference(DumpDateDifference):
 
     template = "CAST(JULIANDAY({lop}) - JULIANDAY({rop}) AS INTEGER)"
                 " ({month} - 1) || ' months', ({day} - 1) || ' days')")
 
 
+class SQLiteDumpMakeDateTime(DumpMakeDateTime):
+
+    def __call__(self):
+        template = ("DATETIME('0001-01-01', ({year} - 1) || ' years',"
+                    " ({month} - 1) || ' months', ({day} - 1) || ' days'")
+        if self.phrase.hour is not None:
+            template += ", {hour} || ' hours'"
+        if self.phrase.minute is not None:
+            template += ", {minute} || ' minutes'"
+        if self.phrase.second is not None:
+            template += ", {second} || ' seconds'"
+        template += ")"
+        self.format(template, self.arguments)
+
+
+class SQLiteDumpCombineDateTime(DumpCombineDateTime):
+
+    template = "({date} || ' ' || {time})"
+
+
 class SQLiteDumpExtractYear(DumpExtractYear):
 
     template = "CAST(STRFTIME('%Y', {op}) AS INTEGER)"
     template = "CAST(STRFTIME('%d', {op}) AS INTEGER)"
 
 
+class SQLiteDumpExtractHour(DumpExtractHour):
+
+    template = "CAST(STRFTIME('%H', {op}) AS INTEGER)"
+
+
+class SQLiteDumpExtractMinute(DumpExtractMinute):
+
+    template = "CAST(STRFTIME('%M', {op}) AS INTEGER)"
+
+
+class SQLiteDumpExtractSecond(DumpExtractSecond):
+
+    template = "CAST(STRFTIME('%f', {op}) AS REAL)"
+
+

test/regress/input/library.yaml

   - uri: /date('2010-13-07')
     expect: 400
 
+  # Time values
+  - uri: /{time('20:13'), time('20:13:04.5')}
+  # Invalid time
+  - uri: /{time('29:04')}
+    expect: 400
+
+  # Datetime values
+  - uri: /{datetime('2010-04-15'), datetime('2010-04-15 20:13'),
+           datetime('2010-04-15T20:13:04.5'),
+           datetime('2010-04-15 20:13:04.5 -0400')}
+  # Invalid Datetime value
+  - uri: /{datetime('2010-13-07 17:43')}
+    expect: 400
+  - uri: /{datetime('2010-06-05 29:04')}
+    expect: 400
+
 ########################################################################
 
 - title: Logical and Comparison Operators
   - uri: /{is_null(null()), is_null('NULL'), is_null(0)}
 
   # Equality
-  - uri: /{true()=true(), 1=1, 'HTSQL'='HTSQL',
-           date('2010-04-15')=date('2010-04-15')}
-  - uri: /{true()!=false(), 1!=0, 'HTSQL'!='PITA',
-           date('2010-04-15')!=date('1991-08-20')}
-  - uri: /{true()!=true(), 1!=1, 'HTSQL'!='HTSQL',
-           date('2010-04-15')!=date('2010-04-15')}
-  - uri: /{true()=false(), 1=0, 'HTSQL'='PITA',
-           date('2010-04-15')=date('1991-08-20')}
+  - uri: /{true()=true(), 1=1, 'HTSQL'='HTSQL'}
+  - uri: /{date('2010-04-15')=date('2010-04-15'),
+           time('20:13')=time('20:13'),
+           datetime('2010-04-15 20:13')=datetime('2010-04-15 20:13')}
+  - uri: /{true()!=false(), 1!=0, 'HTSQL'!='PITA'}
+  - uri: /{date('2010-04-15')!=date('1991-08-20'),
+           time('20:13')!=time('02:01'),
+           datetime('2010-04-15 20:13')!=datetime('1991-08-20 02:01')}
+  - uri: /{true()!=true(), 1!=1, 'HTSQL'!='HTSQL'}
+  - uri: /{date('2010-04-15')!=date('2010-04-15'),
+           time('20:13')!=time('20:13'),
+           datetime('2010-04-15 20:13')!=datetime('2010-04-15 20:13')}
+  - uri: /{true()=false(), 1=0, 'HTSQL'='PITA'}
+  - uri: /{date('2010-04-15')=date('1991-08-20'),
+           time('20:13')=time('02:01'),
+           datetime('2010-04-15 20:13')=datetime('1991-08-20 02:01')}
   # IN
   - uri: /{5={2,3,5,7}, 'HTSQL'!={'ISBL','SQUARE','QUEL'}}
   - uri: /{5!={2,3,5,7}, 'HTSQL'={'ISBL','SQUARE','QUEL'}}
     expect: 400
   - uri: /{date('2010-04-15')==1991}
     expect: 400
+  - uri: /{date('2010-04-15')=datetime('2010-04-15')}
+    expect: 400
+  - uri: /{datetime('2010-04-15 20:13')!==time('02:01')}
+    expect: 400
 
   # Comparison
-  - uri: /{1<10, 7.0<=7.0, 'omega'>'alpha',
-           date('2010-04-15')>=date('1991-08-20')}
-  - uri: /{1>=10, 7.0>7.0, 'omega'<='alpha',
-           date('2010-04-15')<date('1991-08-20')}
+  - uri: /{1<10, 7.0<=7.0, 'omega'>'alpha'}
+  - uri: /{date('2010-04-15')>=date('1991-08-20'),
+           time('02:01')<time('20:13'),
+           datetime('2010-04-15 20:13')>datetime('1991-08-20 02:01')}
+  - uri: /{1>=10, 7.0>7.0, 'omega'<='alpha'}
+  - uri: /{date('2010-04-15')<date('1991-08-20'),
+           time('02:01')>=time('20:13'),
+           datetime('2010-04-15 20:13')<=datetime('1991-08-20 02:01')}
   # NULL
   - uri: /{0<null(), ''>null(), null()>=null()}
   # Coercion
     expect: 400
   - uri: /{date('2010-04-15')>=1991}
     expect: 400
+  - uri: /{datetime('2010-04-15 20:13')>time('02:01')}
+    expect: 400
 
   # If NULL and NULL If
   - uri: /{if_null('Victor', 'William'),
     expect: 400
   - uri: /{if_null(date('2010-04-15'),1991)}
     expect: 400
-  - uri: /{null_if(date('2010-04-15'),1991)}
+  - uri: /{null_if(datetime('2010-04-15 20:13'),time('02:01'))}
     expect: 400
 
   # If and Switch
   - uri: /{integer(string('cinq'))}
     expect: 409
     ignore: true
-    ifdef: [pgsql, mssql]
+    ifndef: [sqlite, mysql]
   # Integer overflow
   - uri: /{integer(4294967296.0)}
     expect: 409
     ifdef: [pgsql, mssql]
   - uri: /{integer(4294967296.0),
            integer(1.8446744073709552e+19)}
-    ifdef: [sqlite, mysql]
+    ifndef: [pgsql, mssql]
 
   # Arithmetics
   - uri: /{+7, -7, +2.125, -2.125, +271828e-5, -271828e-5}
     ignore: true
     ifdef: [pgsql, mssql]
   - uri: /{7*2147483647, 9223372036854775807+1}
-    ifdef: [sqlite, mysql]
+    ifndef: [pgsql, mssql]
   # Division by zero
   - uri: /{7/0}
     expect: 409
     ignore: true
-    ifdef: [pgsql, mssql]
+    ifndef: [sqlite, mysql]
   - uri: /{7/0.0}
     expect: 409
     ignore: true
-    ifdef: [pgsql, mssql]
+    ifndef: [sqlite, mysql]
   - uri: /{7/0e0}
     expect: 409
     ignore: true
-    ifdef: [pgsql, mssql]
+    ifndef: [sqlite, mysql, oracle]
   - uri: /{7/0, 7/0.0, 7/0e0}
     ifdef: [sqlite, mysql]
+  - uri: /{0e0/0e0, 7/0e0}
+    ifdef: [oracle]
 
   # Rounding
   - uri: /{round(3272.78125,2)}
   - uri: /{string(true()), string(false())}
   - uri: /{string(60), string(2.125), string(-57721e-5)}
   - uri: /{string(date('2010-04-15'))}
+  - uri: /{string(time('20:13'))}
+  - uri: /{string(datetime('2010-04-15 20:13'))}
 
   # Length
   - uri: /{length(''), length('OMGWTFBBQ')}
 
   # Conversion
   - uri: /{date(null()), date('2010-04-15')}
+  - uri: /{date(string('2010-04-15'))}
+  - uri: /{date(datetime('2010-04-15 20:13'))}
   # Inadmissible operand
   - uri: /{date('2010-13-07')}
     expect: 400
     expect: 400
   - uri: /{date(7)}
     expect: 400
+  - uri: /{date(time('20:13'))}
+    expect: 400
   # Not a date
   - uri: /{date(string('birthday'))}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifndef: [sqlite, mysql]
   - uri: /{date(string('2010-13-07'))}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifndef: [sqlite, mysql]
   - uri: /{date(string('birthday')),
            date(string('2010-13-07'))}
     ifdef: [sqlite, mysql]
            month(date('2010-04-15')),
            day(date('2010-04-15'))}
 
-  # Arithmetics
+  # Arithmetics 
   - uri: /{date('1991-08-20')+6813,
            date('2010-04-15')-6813,
            date('2010-04-15')-date('1991-08-20')}
 
 ########################################################################
 
+- title: Time Functions and Operators
+  tests:
+
+  # Conversion
+  - uri: /{time(null()), time('20:13')}
+  - uri: /{time(string('20:13'))}
+    ifndef: oracle
+  - uri: /{time(string('20:13:00'))}
+    ifdef: oracle
+  - uri: /{time(datetime('2010-04-15 20:13'))}
+  # Inadmissible operand
+  - uri: /{time('29:04')}
+    expect: 400
+  - uri: /{time(true())}
+    expect: 400
+  - uri: /{time(7)}
+    expect: 400
+  - uri: /{time(date('2010-04-15'))}
+    expect: 400
+  # Not a datetime value
+  - uri: /{time(string('just a moment ago'))}
+    expect: 409
+    ignore: true
+    ifndef: [sqlite, mysql]
+  - uri: /{time(string('29:04'))}
+    expect: 409
+    ignore: true
+    ifndef: [sqlite, mysql]
+  - uri: /{time(string('just a moment ago')),
+           time(string('29:04'))}
+    ifdef: [sqlite, mysql]
+
+  # Components
+  - uri: /{hour(time('20:13:04.5')),
+           minute(time('20:13:04.5')),
+           second(time('20:13:04.5'))}
+
+########################################################################
+
 - title: DateTime Functions and Operators
   tests:
 
+  # Conversion
+  - uri: /{datetime(null()), datetime('2010-04-15 20:13')}
+  - uri: /{datetime(string('2010-04-15 20:13'))}
+    ifndef: oracle
+  - uri: /{datetime(string('2010-04-15 20:13:00'))}
+    ifdef: oracle
+  - uri: /{datetime(date('2010-04-15'))}
+  # Inadmissible operand
+  - uri: /{datetime('2010-13-07 17:43')}
+    expect: 400
+  - uri: /{datetime(true())}
+    expect: 400
+  - uri: /{datetime(7)}
+    expect: 400
+  - uri: /{datetime(time('20:13'))}
+    expect: 400
+  # Not a datetime value
+  - uri: /{datetime(string('just a moment ago'))}
+    expect: 409
+    ignore: true
+    ifndef: [sqlite, mysql]
+  - uri: /{datetime(string('2010-13-07 17:43'))}
+    expect: 409
+    ignore: true
+    ifndef: [sqlite, mysql]
+  - uri: /{datetime(string('just a moment ago')),
+           datetime(string('2010-13-07 17:43'))}
+    ifdef: [sqlite, mysql]
+
   # Construction
   - uri: /{now()}
     ignore: true
+  - uri: /{datetime(2010,4,15), datetime(2010,4,15,20,13),
+           datetime(2010,4,15,20,13,4.5)}
+  - uri: /{datetime(2010,3,46,25,-289,124.5)}
+  - uri: /{datetime(date('2010-04-15'),time('20:13'))}
+
+  # Components
+  - uri: /{year(datetime('2010-04-15 20:13:04.5')),
+           month(datetime('2010-04-15 20:13:04.5')),
+           day(datetime('2010-04-15 20:13:04.5'))}
+  - uri: /{hour(datetime('2010-04-15 20:13:04.5')),
+           minute(datetime('2010-04-15 20:13:04.5')),
+           second(datetime('2010-04-15 20:13:04.5'))}
+
+  # Arithmetics
+  - uri: /{datetime('1991-08-20 02:01')+6813.7583854166667,
+           datetime('2010-04-15 20:13:04.5')-6813.7583854166667}
 
 ########################################################################
 

test/regress/input/translation.yaml

                    school.department:=department?code='acc')
             .school.department.course{department,no,title,credits}
     - uri: /define(double(x):=x*2){double(1),double(2),double(3)}
-    - uri: /define(student.age(date):=(date(date)-dob)/365:round(1),
+    - uri: /define(student.age(date):=(date(date)-date(dob))/365:round(1),
                    student.age():=age('2011-01-01'),
                    inactive_student:=student?!is_active)
             .inactive_student{name,dob,start_date,age(start_date),age()}

test/regress/output/mssql.yaml

             encode error: invalid date literal: month must be in 1..12:
                 /date('2010-13-07')
                  ^^^^^^^^^^^^^^^^^^
+        - uri: /{time('20:13'), time('20:13:04.5')}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             |                                    |
+            -+------------------------------------+-
+             | time('20:13') | time('20:13:04.5') |
+            -+---------------+--------------------+-
+             | 20:13:00      | 20:13:04.500000    |
+                                            (1 row)
+
+             ----
+             /{time('20:13'),time('20:13:04.5')}
+             SELECT 0.84236111111111112e0,
+                    0.8424131944444444e0
+        - uri: /{time('29:04')}
+          status: 400 Bad Request
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |
+            encode error: invalid time literal: hour must be in 0..23:
+                /{time('29:04')}
+                  ^^^^^^^^^^^^^
+        - uri: /{datetime('2010-04-15'), datetime('2010-04-15 20:13'), datetime('2010-04-15T20:13:04.5'),
+            datetime('2010-04-15 20:13:04.5 -0400')}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             |                                                                                                                                     |
+            -+-------------------------------------------------------------------------------------------------------------------------------------+-
+             | datetime('2010-04-15') | datetime('2010-04-15 20:13') | datetime('2010-04-15T20:13:04.5') | datetime('2010-04-15 20:13:04.5 -0400') |
+            -+------------------------+------------------------------+-----------------------------------+-----------------------------------------+-
+             | 2010-04-15             | 2010-04-15 20:13:00          | 2010-04-15 20:13:04.500000        | 2010-04-15 20:13:04.500000              |
+                                                                                                                                             (1 row)
+
+             ----
+             /{datetime('2010-04-15'),datetime('2010-04-15 20:13'),datetime('2010-04-15T20:13:04.5'),datetime('2010-04-15 20:13:04.5 -0400')}
+             SELECT CAST('2010-04-15 00:00:00' AS DATETIME),
+                    CAST('2010-04-15 20:13:00' AS DATETIME),
+                    CAST('2010-04-15 20:13:04.500' AS DATETIME),
+                    CAST('2010-04-15 20:13:04.500' AS DATETIME)
+        - uri: /{datetime('2010-13-07 17:43')}
+          status: 400 Bad Request
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |
+            encode error: invalid datetime literal: month must be in 1..12:
+                /{datetime('2010-13-07 17:43')}
+                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+        - uri: /{datetime('2010-06-05 29:04')}
+          status: 400 Bad Request
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |
+            encode error: invalid datetime literal: hour must be in 0..23:
+                /{datetime('2010-06-05 29:04')}
+                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
       - id: logical-and-comparison-operators
         tests:
         - uri: /{true(), false()}
              SELECT 1,
                     0,
                     0
-        - uri: /{true()=true(), 1=1, 'HTSQL'='HTSQL', date('2010-04-15')=date('2010-04-15')}
-          status: 200 OK
-          headers:
-          - [Content-Type, text/plain; charset=UTF-8]
-          body: |2
-             |                                                                                |
-            -+--------------------------------------------------------------------------------+-
-             | true()=true() | 1=1  | 'HTSQL'='HTSQL' | date('2010-04-15')=date('2010-04-15') |
-            -+---------------+------+-----------------+---------------------------------------+-
-             | true          | true | true            | true                                  |
-                                                                                        (1 row)
-
-             ----
-             /{true()=true(),1=1,'HTSQL'='HTSQL',date('2010-04-15')=date('2010-04-15')}
+        - uri: /{true()=true(), 1=1, 'HTSQL'='HTSQL'}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             |                                        |
+            -+----------------------------------------+-
+             | true()=true() | 1=1  | 'HTSQL'='HTSQL' |
+            -+---------------+------+-----------------+-
+             | true          | true | true            |
+                                                (1 row)
<