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"
