yes, you are right there is difference between "should" and "may". I "should" use "may" in the first place.
However, I do not think the interpretation of a timestamp is entirely up to the user (Do you mean the end user of a database product?). What if the implementation of database takes a different interpretation? For instance, when the system has to do conversion between a timestamp w/ tz and a timestamp w/o tz, "SQL assumes the value without time zone to be local, subtracts the current default time zone displacement of the SQL-session from it to give UTC, and associates that time zone displacement with the result." I think what we are talking about is the semantics of those different data types in a database system; it has nothing to do whether the system is using JVM, or running on linux or other operation system. On Thu, Mar 16, 2017 at 5:39 PM, Julian Hyde <[email protected]> wrote: > The difference between “should” and “may”. The interpretation of a timestamp > is entirely up to the user. > > If I am reading a timestamp value from a database, and I know it to be in > local timezone, I use the ResultSet.getTimestamp(int) method, which (per the > JDBC spec) interprets the value as being in my JVM’s time zone, and converts > it into an instant (a java.sql.Timestamp) accordingly. Thus ‘1970-01-01 > 00:00:00’ will become ‘1969-12-31 16:00:00 UTC’ since I am in pacific time. > > If I am reading a timestamp value from a database, and I know it to be in > some other timezone, I use the ResultSet.getTimestamp(int, Calendar) method, > which applies the time zone inside the calendar. > > I HAVE to provide a timezone, implicitly or explicitly, when reading a > TIMESTAMP value from a database via JDBC into a java.sql.Timestamp. Why? > Because I am converting a zoneless value into an instant. > > Note that the database can do quite a few operations on a timestamp without > knowing its time zone. For instance "CAST(ts AS VARCHAR)" and "EXTRACT(HOUR > FROM ts)” and “ts + INTERVAL ‘1’ DAY” all make sense. > > Julian > > > >> On Mar 16, 2017, at 4:54 PM, Jinfeng Ni <[email protected]> wrote: >> >> On Thu, Mar 16, 2017 at 4:41 PM, Julian Hyde <[email protected] >> <mailto:[email protected]>> wrote: >>> >>>> On Mar 16, 2017, at 4:25 PM, Jinfeng Ni <[email protected]> wrote: >>>> >>>> Time/Timestamp without t/z should be interpreted as local time. >>> >>> >>> No. >>> >>> If I am in pacific time and I have a TIMESTAMP value “1970-01-01 12:00:00” >>> and I send it to you in central european time you receive a TIMESTAMP value >>> “1970-01-01 12:00:00”. >>> >>> Its time zone is not my local time zone, or your local time zone, or UTC. >>> It has no time zone. >>> >>> >> >> Maybe I did not read the SQL 2011 ISO/IEC 9075-2:2011(E) correctly. >> How do we interpret the following ? >> >> "A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP >> WITHOUT TIME ZONE, may represent a local time" >
