0

follow this question: how to use utc in postgres timestamp with jdbc PrepareStatement parameter?

now i use 

new java.sql.Timestamp(date.getTime() + date.getTimezoneOffset() * 60000)

to upload date value, and 

tstmp.getTime() - tstmp.getTimezoneOffset() * 60000

to download value from postgres.

but, date.getTime() is milliseconds from 1970-01-01 of UTC timezone, it's static and not related to client timezone, see below

new Date(1443145546920)
Fri Sep 25 2015 09:45:46 GMT+0800 (中国标准时间)
*Now i change timezone from Beijing/Shanghai to Tokyo*
new Date(1443145546920)
Fri Sep 25 2015 10:45:46 GMT+0900 (Japan Standard Time)

look, 1443145546920 should always be Fri Sep 25 2015 01:45:46 +0000, never change whatever my client TimeZone is.

so, the right way should is:

      client(utc+8)                                   dbserver(utc)
  upload time 1443145546920 without tzoffset 
                                                  write 1443145546920 
  download 
                                                  send 1443145546920
  got 1443145546920 and add client tzoffset 480m

but postgres jdbc show me this way:

      client(utc+8)       jdbc of pg                  dbserver(utc)
  upload time 1443145546920 + tzoffset 
                      - tzoffset 480m
                                                  write 1443145546920 
  download 
                                                  send 1443145546920
                      + tzoffset 480m
  got 1443145546920 + tzoffset
  manual substract tzoffset
Community
  • 1
  • 1
Inshua
  • 1,355
  • 1
  • 12
  • 12
  • Use the precious title space to describe the purported 'bug'. If such *is* a bug, let it form naturally. – user2864740 Sep 25 '15 at 02:11
  • well, my english is poor, may you give me a brief title about it? – Inshua Sep 25 '15 at 02:16
  • Please show the table definition too. In particular, are you using timestamp or timestamp with time zone? – Craig Ringer Sep 25 '15 at 02:31
  • no table, just a test sql: "select to_json(?::timestamp) as a, to_json(current_timestamp::timestamp) as b", see http://stackoverflow.com/questions/32757700/how-to-use-utc-in-postgres-timestamp-with-jdbc-preparestatement-parameter/32758245#32758245. i know timestamp with time zone is better choice, but Javascript dont support Date.setTimezoneOffset, so utc is the only way. – Inshua Sep 25 '15 at 03:04
  • Does this answer your question: http://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific – Mark Rotteveel Sep 25 '15 at 08:10

0 Answers0