Saving and retrieving time from client to database

 

This question already has an answer here:

  • Daylight saving time and time zone best practices [closed] 30 answers

    • Yours is a duplicate of many other Questions. So I'll be brief.

    There are two ways to represent three kinds of date-time values:

  • Store and exchange date-time values in UTC if actual moment has passed ( payment made, invoice received, court papers served, shipment arrived, etc.), or if in the future you know you want a specific moment on the timeline regardless if politicians redefine the time zone such as changing Daylight Saving Time (DST). For Java, use Instant class. In SQL use TIMESTAMP WITH TIME ZONE .
  • Store and exchange date-time values without zone/offset for scheduling future events far enough out in the future that politicians may change the time zone rules (dental appointment next year, interview in two months, party announcement, etc.). Politicians around the world have shown a penchant for twiddling with our clocks, often with little warning, sometimes only several weeks advance notice. For Java, use LocalDateTime class. In SQL, use TIMESTAMP WITHOUT TIME ZONE . To present a schedule, generate transient data in Java using ZonedDateTime class, via Instant::atZone method.
  • Store and exchange date-time values without zone/offset for non-specific events , such as the start of Christmas or saying "all our factories in Delhi, Düsseldorf, & Detroit, will close for a half-day at noon on October 30th this year" where we mean noon at each of their respective local times rather than at a simultaneous moment. For Java, use LocalDateTime class. In SQL, use TIMESTAMP WITHOUT TIME ZONE .

    • As a programmer and sysadmins, learn to think in UTC and in 24-hour time. Forget about your own parochial time zone while on the job. Keep a second clock set to UTC in the office.

    How do you know the user's expected/desired time zone? Ultimately the only sure way is to ask her/him.

    When serializing to text, use standard ISO 8601 formats.

    Avoid the terrible old date-time classes such as java.util.Date and java.sql.Date , etc. These are now legacy, supplanted by the excellent industry-leading java.time classes.

    An Instant represents a moment on the timeline in UTC. 

    Instant instant = Instant.ofEpochMilli( millis ) ;

    For presentation to user, adjust the UTC into a time zone they expect/desire. Think of this like internationalization, where you store references to a key, then for presentation use the retrieved key to look up a localized text value.

    Never use 3-4 character pseudo-time zones. True time zones have a continent/region name such as Asia/Kolkata and Pacific/Auckland . 

    ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

    Note that databases vary widely in their handling of date-time. The SQL standard barely touches on the subject. Study the docs and conduct experiments to be sure you understand it's behavior. Ditto for your JDBC driver. Tip: Postgres has some of the best support for date-time types and functions.

    With JDBC 4.2 and later, exchange data with database via java.time objects, by calling:

  • PreparedStatement::setObject
  • ResultSet::getObject

    • Example code 

    myPStmt.setObject.( … , myInstant ) ;

    …and… 

    Instant instant = myResultSet.getObject( … , Instant.class ) ;

    When you're storing time, your best bet is to store it everywhere in epoch time (or unix time if you prefer), and deserialize it on the front end. This means that you can have confidence that you've persisted the correct time, and then you can convert it on the front end. Without knowing the particulars, i suspect that the date object you're creating in Java is causing the offshift, and then you're storing the wrong date value in the database.

    链接地址: http://www.djcxy.com/p/29350.html

    上一篇: 什么是存储DateTime的最佳方式？

    下一篇: 保存和检索从客户端到数据库的时间