FarragoDatetimeTypes
From Eigenpedia
This document discusses date, time, and timestamp types (datetimes) within Farrago. Currently, Farrago supports a subset of the SQL standard, datetimes "without time zone". Note that in this document, we sometimes use slightly different wording than the SQL spec.
Contents |
The SQL Standard (Part 2 Chapter 4.6)
SQL 2003 describes two kinds of datetime types. Both have basic attributes, such as day, month, and year. However, one kind (without time zone) is plain data with no notion of time zone or actual time, much like a string. The other kind (with time zone) represents an instant in GMT time, along with a time offset. The time offset describes the time offset that was in effect for the datetime when it was created. For example:
It is possible to convert directly from a datetime with time zone to a datetime without timezone. A conversion in the other direction relies on a session time zone. (SQL specifies a "time zone" as a time offset, but Oracle allows a proper time zone as an extension, and can therefore handle Daylight Saving Time.)
Zoneless time = GMT time + Explicit time offset [1]
GMT time = Zoneless time - Session time offset [2]
Time Intervals
Time intervals are described in Section 4.6.3. There are two kinds of intervals, Year/Month intervals and Day/Time intervals. Intervals can cover a range of fields allowed within the interval type (for example, DAY to SECOND) or they may cover a single field (ex: DAY). An interval such as MONTH to DAY is not allowed. Normally fields can have two digits, but DAY and YEAR are allowed to have as many as the specified "start precision" and SECOND is allowed to have "seconds precision".
Allowed operations include
- Subtracting two dates to get an interval
- Adding or subtracting an interval from a date
- Arithmetic (+/-) between intervals
- Multiplying and dividing intervals by numbers
- Casting to strings or perhaps exact numeric (when there is only one field)
- Extracting fields of an interval
Daylight Saving Time, etc.
In describing the general way times work, the SQL spec states, "where applicable, a time zone offset changes at the beginning and end of Daylight time." However the spec does not provide a mechanism by which date arithmetic could change a time zone offset. Note that due to DST there is no "2:30am on Apr 2, 2006" if the local time zone is PST. And "1:30am on Oct 29" may occur twice. The SQL spec does not concern itself with such issues. The SQL spec also touches on leap seconds used to adjust UTC, but does not say much or require anything.
Farrago implementation
Type Protocol
Farrago uses the class ZonelessDatetime to represent datetimes without a time zone. This differentiates them from Jdbc (java.sql) types. Since Jdbc is used as a communication protocol, the Farrago server deals with both type families at the same time. Since Farrago clients work with data valid in a client-specific to the time zone, a client library is required to convert the client-specific data into a canonical form (ZonelessDatetime).
Encoding
In order to leverage Java libraries, Farrago encodes datetimes as long values matching the convention of java.util.Date for GMT times. Since tha value is expressed in milliseconds and GMT has no offsets, date and time components may conveniently be extracted as follows:
DateComponent = Value / MillisecondsPerDay
TimeComponent = Value % MillisecondsPerDay
The Timestamp data type has both components. The Time data type has no date component, while the Date data type has no time component. The date component is relative to 1970-01-01. It is possible to parse and format datetime strings using a Java SimpleDateFormat set to the GMT time zone.
Day/Time intervals are also encoded as milliseconds values. Year/Month intervals are encoded as months.
Datetime Arithmetic
With datetimes and intervals all encoded as long values, datetime arithmetic can reduced to long (Bigint) arithmetic. This is true as long as all values are in milliseconds. However, it does not hold for Year/Month intervals. To get the most functionality with limited resources, Farrago implements Day/Time intervals, but not Year/Month intervals. Arithmetic operations include:
Table. Result of datetime arithmetic operations
| Operation | Result |
| Date - Date | Interval |
| Date + or - Interval | Date |
| Interval + or - Interval | Interval |
| Interval * or / Integer | Interval |
| Extract field from Interval | Integer |
Details
Farrago describes an interval using an SqlIntervalQualifier. It contains the start and end TimeUnit, and precision fields. There are two Rel data types, one for each kind of interval. Rather than focusing on SQL compliance, the Farrago implementation focuses on functionality. To enable interval arithmetic, Farrago introduces non-standard casts: (1) Datetimes to Bigint and (2) Intervals to Bigint. (Farrago discards the usual interval to numeric cast, whose functionality is available through extract.)
Interval arithmetic is implemented via a "Convertlet". These snippets translate one kind of operation into another. For example, subtracting two dates:
StandardConvertletTable.convertDatetimeMinus(datetime arg1, datetime arg2) cast( cast(arg1 as bigint) - cast(arg2 as bigint) as interval ...)
Extract is also implemented as a Convertlet. For example, to extract hours from an interval:
// mask out larger units millis = millis % millis_in_day // select desired units millis / millis_in_hour
Datetime Support
The following tables describe code used to support datetimes. The "status" column indicates any known problems.
Literals
(As entered via SQL. See SqlAbstractDateTimeLiteral and children, also see RexLiteral.fromJdbcString)
| Description | Implementation | Notes | Status |
| Parse literal string | Parse via DateTimeUtil, with time zone set to GMT | Perhaps it would be safer to use ZonelessDatetime parsing functions | ok |
| Format literal | Formatted via a ZonelessDatetime, append precision | Precision not handled consistently | ok |
Farrago Runtime
(SqlDateTimeWithoutTZ and inner classes. Rather than store a long field for value, a ZonelessDatetime field is stored. This requires some trickery with FarragoTypeFactory to access datetime values. Since we want to return ZonelessDatetime, a non-Jdbc value, to the Jdbc driver, we need to modify the way FarragoTupleIterResultSet obtains a raw value. When AbstractResultSet gets the raw object, it must convert it into a Jdbc value for getObject() to fulfill the Jdbc ResultSet contract.)
| Category | Implementation | Notes | Status |
| Cast string to datetime | Parse via ZonelessDatetime | ok | |
| Initialize from long value | Directly assign to internal value | Assuming that we assign from the same type | ok |
| Assign from datetime | ZonelessDatetime set zoneless time method | Copies value | ok |
| Assign from Jdbc | ZonelessDatetime set zoned time method | Subtracts time zone offset | ok |
| Format as string | ZonelessDatetime toString | ok | |
| Get nullable data | Returns value as a Jdbc type, relative to server time zone | Used for UDRs (user defined routines) | ok |
| Get special data | Returns value as a ZonelessDatetime | Sent to Jdbc Driver, for conversion to Jdbc type relative to client time zone | ok |
Dynamic parameters
(Such as for prepared JDBC statements with wildcards: "insert into t values (?, ?)". See FarragoJdbc...ParamDef. Each wildcard validates to a target type. Then the client calls setXXX, triggering a conversion from client data to server data.)
| Category | Implementation | Notes | Status |
| String to datetime | ZonelessDatetime parse functions | Client time zone is irrelevant | ok |
| Client to server datetime | ZonelessDatetime set zoned time method | Relies on provided Calendar | ok |
External data
Since the runtime differentiates between ZonelessDatetime and Jdbc values, we should now be able to handle external Jdbc data without modifying it.
Current_Date, etc.
These functions should run relative to the session time zone. But currently, they run relative to the server time zone.
Timestamp with Local Time Zone
Two major shortcomings of timestamps, as described in the SQL spec are
- Timestamps values are not localized when viewed in different time zones. 2006-07-01 12:00:00 will look the same, whether viewed from PST or GMT or anywhere else. A global organization, that deals with times all over the world might want their times converted into their current time zone. 2006-07-01 12:00:00 EST means 2006-07-01 10:00:00 PST.
- The spec ignores Daylight Saving Time. In PST, 2006-04-02 00:30:00 + 2 hours might equal 2006:04:02 02:30:00, a time which doesn't exist. There is no way to differentiate between the two values for 2006-10-29 01:30:00.
At one point in time, Farrago had a Java-like timestamp without these limitations. However this functionality was not maintained due to code complexity. In the future, we may want to reintroduce this functionality as a non-standard feature, as Oracle has done with its "timestamp with local time zone" type.
Note: in the United States, Daylight Savings Time starts on the 1st Sunday of April and reverts the last Sunday of October. (In 2006 it runs from Apr 2 to Oct 29.) The Energy Policy Act of 2005 lengthens DST for an indefinite trial period starting 2007. It will start earlier (2nd Sunday of March) and end later (1st Sunday of November). For DST, we spring forward at "2:00" i.e. move from "1:59" PST to "3:00" PDT and fall backwards at "2:00" i.e. move from 1:59 PDT to 1:00 PST.
eigenjira:FRG-272 tracks a bug having to do with points in time corresponding to the DST transition hours which are meaningful in some timezones but not others.
