java postgresql date,使用Java在PostgreSQL中存储时间最为推荐的方法是什么？

I'm storing two dates in the PostgreSQL database. First, is the data of visit of a webpage, and the second date is the date of last modification of the webpage(this is get as a long).

I have some doubts what is the best strategy to store these values.

I only need day/month/year and hour:seconds and this will only for statistical proposes.

So, some doubts:

is best store as long and convert on recover of information or store in the data format above?

is best set the date of visit on the software or in the insertion in the database?

in Java, how are the best classes to handle dates?

解决方案

If you want to record the physical instant at when a particular event (typically some creation/modification/deletion) ocurred, (a true "timestamp"), I suggest:

Java: java.util.Date class - or, if using, Jodatime (highly recommended), an Instant.

JDBC: java.sql.Timestamp

Postgresql: TIMESTAMP WITH TIMEZONE (TIMESTAMPTZ)

(Don't be fooled with Postgresql datatype names: WITH TIMEZONE/WITHOUT TIMEZONE have very peculiar meanings, none of them actually stores a timezone)

Some boilerplate code for this case: the following assumes that ps is a PreparedStatement, rs a ResultSet and calendarUTC is a static Calendar object corresponding to UTC.

Write to database:

long milis = date.getTime() ; // or instant.getMillis() or whatever

ps.setTimestamp(colNum,new Timestamp(milis), calendarUTC); // column is TIMESTAMPTZ!

Read from database:

public static final Calendar tzUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

...

Timestamp ts = rs.getTimestamp(colnum,tzUTC); // column is TIMESTAMPTZ

return ts !=null ? new Date(ts.getTime) : null;

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).

"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".

If, instead of a timestamp (an instant on the physical timeline), you want to record safely a "civil" local date-time (that is, the fields {year-month-day hour-min-sec} as a whole), you should use a TIMESTAMP WITHOUT ZONE and use a slightly different logic. For the sake of completeness (here I use Joda's LocalDateTime; Java's Date API is painful).

Read from database:

public static final DateTimeZone jodaTzUTC = DateTimeZone.forID("UTC");

...

Timestamp ts = getter.getTimestamp(tzUTC);

return ts != null ? new LocalDateTime(ts.getTime(), jodaTzUTC ) : null;

Write to database:

Timestamp ts = new Timestamp(localdatime.toDateTime(jodaTzUTC).getMillis())

ps.setTimestamp(colNum,ts, tzUTC); // column is TIMESTAMP !

Again, this strategy is safe and you can sleep in peace: if you store '2011-10-30 23:59:30' , you'll always retrieve those precise fields (year=2011, month=10... etc), no matter what, even if tomorrow someone changes the timezone of Postgresql, of your JVM or OS timezone, or if your country updates its DST rules, etc.

Added: If you want (it seems a natural requirement) to store the full datetime specification (a Datetime in Jodatime: the timestamp together with the timezone, which implicitly also includes the full civil datetime info - plus the timezone), you are out of luck: Postgresql hasn't a datatype for this (neither other databases, to my knowledge). 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).