![]() ![]() I guess it would be good practice to use timestamp without timezone when all your data is for sure in the same zone, or you got another layer over there which handles the time conversion. If you are using the timestampsTz() you will not safe the timezone. If you are using the timestamps() function you will also store the timezone that was chosen by your environment. The normal timestamps() is more a representation like normal clock. The adjustment to this time is made by the timezone that is related to your system. ![]() TimestampsTz() is a representation for a specific point in time. You must devise your own storage, perhaps in a pair of fields: could be the two above types (highly redundant, though efficient for retrieval and calculation), or one of them plus the time offset (you lose the timezone info, some calculations become difficult, and some impossible), or one of them plus the timezone (as string some calculations can be extremely costly).Basicly timestampsTz() stands for Timestamp with Timezone while timestamps() is like Timestamp without timezone then I have bad news for you: PostgreSQL hasn't a datatype for this (neither other databases, to my knowledge). etc) always, no matter what - even if tomorrow the timezone of your Postgresql server (or client) changes, or your JVM or your OS timezone, or if your country modifies its DST rules, etc.Īdded: If you want (it seems a natural requirement) to store the full datetime specification (a ZonedDatetime: the timestamp together with the timezone, which implicitly also includes the full civil datetime info - plus the timezone). Ts = new Timestamp(localDt.toInstant(ZoneOffset.UTC).toEpochMilli()), tzUTC) Īgain, this strategy is safe and you can sleep peacefully: if you stored 23:59:30, you'll retrieve those precise fields (hour=23, minute=59. Write LocalDateTime to database TIMESTAMP: Timestamp ts = null LocalDt = LocalDateTime.ofInstant(Instant.ofEpochMilli(ts.getTime()), ZoneOffset.UTC) Read LocalDateTime from database TIMESTAMP: Timestamp ts = rs.getTimestamp(col, tzUTC) // PostgreSQL: TIMESTAMP WITHOUT TIMEZONE ( TIMESTAMP).Java: LocalDateTime (Java 8, or Jodatime).If, instead of a timestamp (an instant on the physical timeline), you are dealing with a "civil" local date-time (that is, the set of fields ), you'd use: "Safely" means that the result will not depend on server or database timezone, or timezones information: the operation is fully reversible, and whatever happens to timezones settings, you'll always get the same "instant of time" you originally had on the Java side. This works safely if your PG type is TIMESTAMPTZ (In that case, the calendarUTC has no effect in that code but it's always advisable to not depend on defaults timezones). Instant inst = ts !=null ? ts.toInstant() : null Read Instant from database TIMESTAMPTZ: Timestamp ts = rs.getTimestamp(col,tzUTC) // column is TIMESTAMPTZ Ps.setTimestamp(col, ts, tzUTC) // column is TIMESTAMPTZ! Timestamp ts = instant != null ? om(instant) : null Write Instant to database TIMESTAMPTZ: Instant instant =. public static final Calendar tzUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC")) Some boilerplate code: the following assumes that ps is a PreparedStatement, rs a ResultSet and tzUTC is a static Calendar object corresponding to UTC timezone. (Don't let PostgreSQL peculiar datatypes WITH TIMEZONE/ WITHOUT TIMEZONE confuse you: none of them actually stores a timezone) PostgreSQL: TIMESTAMP WITH TIMEZONE ( TIMESTAMPTZ).If you want to record the physical instant at when a particular event ocurred, (a true " timestamp", typically some creation/modification/deletion event), then use: So, you need to decide between an Instant or a LocalDateTime datatype. Currently, PostgreSQL (as most databases) doesn't have a datatype to store a full date-and-time with timezone.Your solution should never depend on the server or client timezone setting. ![]() Any strategy for storing date-and-time data in PostgreSQL should, IMO, rely on these two points: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |