![(please configure the [header_logo] section in trac.ini)](/chrome/site/your_project_logo.png){kind=logo}
Date and Time related functions, methods, operators and attributes
HTSQL supports the following date and time related domains:
- domains.Date
- a domain that represents a date of a Gregorian calendar, corresponds to SQL's DATE and Python's datetime.date().
- domains.Time
- a domain that represents time of a day (with or without a time zone), a value in the range from 00:00:00.000000 to 23:59:59.999999, corresponds to SQL's TIME and Python's datetime.time().
- domains.DateTime
- a pair of a date and a time, corresponds to SQL's TIMESTAMP and Python's datetime.datetime().
- domains.TimeDelta
- a domain that represents a difference between two DateTime values, corresponds to SQL's INTERVAL and Python's datetime.timedelta().
Constructors
| Function | HTSQL | SQL | Javascript | Python |
| Create a Date value | date(s), date(Y,M,D) | DATE 's', CAST(v AS DATE) | new Date(s), new Date(Y,M,D) | datetime.date(Y,M,D) |
| Create a Time value | time(s), time(h,m,s) | TIME 's', CAST(v AS TIME) | -- | datetime.time(h,m,s,ms) |
| Create a Time value with time zone | timetz(s), timetz(h,m,s,tz_h,tz_m) | TIMETZ 's', CAST(v AS TIMETZ) | -- | datetime.time(h,m,s,ms,tz) |
| Create a DateTime value | datetime(s), datetime(Y,M,D,h,m,s) | TIMESTAMP 's', CAST(v AS TIMESTAMP) | new Date(s), new Date(Y,M,D,h,m,s,ms) | datetime.datetime(Y,M,D,h,m,s,ms) |
| Create a DateTime value with time zone | datetimetz(s), datetimetz(Y,M,D,h,m,s,tz_h,tz_m) | TIMESTAMPTZ 's', CAST(v AS TIMESTAMPTZ) | -- | datetime.datetime(Y,M,D,h,m,s,ms,tz) |
| Create a TimeDelta value | timedelta(s), timedelta(D,h,m,s) | INTERVAL 's', CAST(v AS INTERVAL) | -- | datetime.timedelta(D,s,ms) |
Signatures for converters:
- htsql:date(Untyped | Date | DateTime value) => Date
- htsql:time(Untyped | Time | DateTime value) => Time(with_tz=False)
- htsql:timetz(Untyped | Time | DateTime(with_tz=True) value) => Time(with_tz=True)
- htsql:datetime(Untyped | Date | DateTime value) => DateTime(with_tz=False)
- htsql:datetimetz(Untyped | Date | DateTime value) => DateTime(with_tz=True)
- htsql:timedelta(Untyped | TimeDelta | Time(with_tz=False) value) => TimeDelta
Signatures for constructors:
- htsql:date(Number year, Number month, Number day) => Date
- htsql:time(Number hour, Number minute, Number second=0.0) => Time(with_tz=False)
- htsql:timetz(Number hour, Number minute, Number second=0.0, Number tz_hour=null(), Number tz_minute=null()) => Time(with_tz=True)
- htsql:datetime(Number year, Number month, Number day, Number hour, Number second=0.0) => DateTime(with_tz=False)
- htsql:datetimetz(Number year, Number month, Number day, Number hour, Number second=0.0, Number tz_hour=null(), Number tz_minute=null()) => DateTime(with_tz=True)
- htsql:timedelta(Number day, Number hour, Number minute, Number second=0.0) => TimeDelta
Note that the single-argument versions of the functions could accept an Untyped parameter. In this case, it is parsed by the HTSQL translator into an object instance. Then, on the SQL generation stage, it is translated into a suitable SQL representation.
Consider, for instance, the expression date('2007-01-01'). While parsing, date('2007-01-01') is converted into a datetime.date instance: datetime.date(2007, 1, 1). Then it is translated into SQL as DATE '2007-01-01'.
HTSQL requires the Untyped parameters for the date and time constructors to have the following form:
| Constructor | Format | Example |
| date() | YYYY-MM-DD | 2007-07-13 |
| time(), timetz() | hh:mm[:ss[.ss*]][Z|(+|-)h[h[:mm]]] | 21:34:17.065Z, 21:34:17+3, 21:34 |
| datetime(), datetimetz() | YYYY-MM-DD[( |T)hh:mm[:ss[.ss*]][Z|(+|-)h[h[:mm]]]] | 2007-07-13T21:34:17.065Z, 2007-07-13 21:34:17+03, 2007-07-13T21:34 |
| timedelta() | [+|-]DD*|[[+|-]DD*( |T)]hh:mm[:ss[.ss*]] | 30, -5 13:08, 6T10:18:06.350 |
Note that HTSQL does not support conversion from String values into date and time related domains while the reverse conversion is possible.
The multi-argument versions of the constructors expect numeric values, but they could also accept Untyped and String values. An Untyped value is reduced to a Number; a String value is cast to a Number.
Current date and time
| Function | HTSQL | SQL | Javascript | Python |
| Get the current date | current_date(), today() | CURRENT_DATE | -- | datetime.date.today() |
| Get the current time | current_time() | LOCALTIME | -- | -- |
| Get the current time with time zone | current_timetz() | CURRENT_TIME | -- | -- |
| Get the current date and time | current_datetime() | LOCALTIMESTAMP | new Date() | datetime.datetime.now() |
| Get the current date and time with time zone | current_datetimetz(), now() | CURRENT_TIMESTAMP | -- |
Signatures:
- htsql:current_date() => Date
- htsql:today() => Date
- htsql:current_time() => Time(with_tz=False)
- htsql:current_timetz() => Time(with_tz=True)
- htsql:current_datetime() => DateTime(with_tz=False)
- htsql:current_datetimetz() => DateTime(with_tz=True)
- htsql:now() => DateTime(with_tz=True)
Note that today() is an alias of current_date() and now() is an alias of current_datetimetz().
Arithmetic operations on date and time values
| Function | HTSQL | SQL | Javascript | Python |
| Unary + for a time interval (time interval) | +delta | +delta | -- | +delta |
| Unary - for a time interval (time interval) | -delta | -delta | -- | -delta |
| Sum of a date and a number of days (date) | date+n, n+date | date+n, n+date | -- | date+delta, delta+date |
| Sum of two time intervals (time interval) | delta1+delta2 | delta1+delta2 | -- | delta1+delta2 |
| Sum of a datetime and a time interval (datetime) | datetime+delta, delta+datetime | datetime+delta, delta+datetime | -- | datetime+delta, delta+datetime |
| Difference between two dates (integer) | date1-date2 | date1-date2 | -- | date-date2 |
| Difference between a date and an integer (date) | date-n | date-n | -- | date-delta |
| Difference of two time intervals (time interval) | delta1-delta2 | delta1-delta2 | -- | delta1-delta2 |
| Difference between two datetimes (time interval) | datetime1-datetime2 | datetime1-datetime2 | -- | datetime1-datetime2 |
| Difference between a datetime and a time interval (datetime) | datetime-delta | datetime-delta | -- | datetime-delta |
| Multiplication of a time interval by a number (time interval) | delta*n, n*delta | delta*n, n*delta | -- | delta*n, n*delta |
| Division of a time interval by a number (time interval) | delta div n | delta/n | -- | delta/n |
Signatures:
- htsql:operator:"+()"(TimeDelta | Time delta) => TimeDelta
- htsql:operator:"-()"(TimeDelta | Time delta) => TimeDelta
- htsql:operator:"+"(Date d, Number(is_integer=True) n) => Date, htsql:operator:"+"(Number(is_integer=True) n, Date d) => Date
- htsql:operator:"+"(TimeDelta | Time delta1, TimeDelta | Time delta2) => TimeDelta
- htsql:operator:"+"(DateTime | Date dt, TimeDelta | Time delta) => DateTime, htsql:operator:"+"(TimeDelta | Time delta, DateTime | Date dt) => DateTime
- htsql:operator:"-"(Date d1, Date d2) => Number(is_integer=True)
- htsql:operator:"-"(Date d, Number(is_integer=True) n) => Date
- htsql:operator:"-"(TimeDelta | Time delta1, TimeDelta | Time delta2) => TimeDelta
- htsql:operator:"-"(DateTime | Date dt1, DateTime | Date dt2) => TimeDelta
- htsql:operator:"-"(DateTime | Date dt, TimeDelta | Time delta) => DateTime
- htsql:operator:"*"(TimeDelta | Time delta, Number x) => TimeDelta, htsql:operator:"*"(Number x, TimeDelta | Time delta) => TimeDelta
- htsql:operator:"div"(TimeDelta | Time delta, Number x) => TimeDelta
Note: A date value could be used anywhere where a datetime value is allowed; in this case, a date value is implicitly converted to a datetime value. Similarly, a time value could be used instead of a timedelta value; a time value is implicitly converted to a timedelta value.
Extract parts of date and time values
| Function | HTSQL | SQL | Javascript | Python |
| Get years of a date, datetime or timedelta value | d.year | EXTRACT(YEAR FROM d) | d.getFullYear() | d.year |
| Get months of a date, datetime or timedelta value | d.month | EXTRACT(MONTH FROM d) | d.getMonth() | d.month |
| Get days of a date, a datetime or a timedelta value | d.day | EXTRACT(DAY FROM d) | d.getDate() | d.day |
| Get hours of a time, a datetime or a timedelta value | t.hour | EXTRACT(HOUR FROM t) | t.getHours() | d.hour |
| Get minutes of a time, a datetime or a timedelta value | t.minute | EXTRACT(MINUTE FROM t) | t.getMinutes() | d.minute |
| Get seconds of a time, a datetime or a timedelta value | t.second | EXTRACT(SECOND FROM t) | t.getSeconds() | d.second+d.microsecond/1000000.0 |
| Get time zone hours of a time or datetime with time zone value | dt.tz_hour | EXTRACT(TIMEZONE_HOUR FROM dt) | -- | -- |
| Get time zone minutes of a time or datetime with time zone value | dt.tz_minute | EXTRACT(TIMEZONE_MINUTE FROM dt) | -- | -- |
Signatures:
- htsql:property:".year"(DateTime | Date | TimeDelta) => Number(is_integer=True)
- htsql:property:".month"(DateTime | Date | TimeDelta) => Number(is_integer=True)
- htsql:property:".day"(DateTime | Date | TimeDelta) => Number(is_integer=True)
- htsql:property:".hour"(DateTime | Time | TimeDelta) => Number(is_integer=True)
- htsql:property:".minute"(DateTime | Time | TimeDelta) => Number(is_integer=True)
- htsql:property:".second"(DateTime | Time | TimeDelta) => Number
- htsql:property:".tz_hour"(DateTime(with_tz=True) | Time(with_tz=True)) => Number(is_integer=True)
- htsql:property:".tz_minute"(DateTime(with_tz=True) | Time(with_tz=True)) => Number(is_integer=True)
