I am working on a project that requires to get the time when a new customer creates his account, and then keep that time in the database for later use. However, I dont really understand the postgresql's timestamp data type? Is it a String a Calendar or Date object relative to java (may you give me an insert and retrieved example)?
1 Answer
As @MadProgrammer mentions, Timestamp and Date are different in both Java and Postgres. While one could use java.sql.Timestamp, I generally represent my Postgres timestamps in POJOs as Strings instead.
The reason for this is that it eliminates a lot of the potential headaches associated with timestamps (timezones, different formats, etc.), as one can simply use a Postgres text representation of a timestamp, and it can still be passed back and forth with Postgres. The exception to this rule would be if you really need it to truly be a Timestamp on the Java end because, say, you're ETL'ing it outside of Postgres to some other platform, and so can't rely on the Postgres text representation the way you could if you were getting data out of Postgres and putting it back into Postgres.
Let's say you had a timestamp that was 2014-08-08 02:11:21.215428, and a table like this:
create table foo
(
id serial,
created timestamp without time zone default now(),
notes text
);
You could represent that in a POJO like this:
class FooBean
{
int id;
String created;
String notes;
};
The key to keep in mind then is if you go to use that value back in another query, you need to cast it back to a timestamp (i.e. notes::timestamp), or else Postgres will throw a cast exception.
Edit in response to comment from OP:
This method helps to obscure timezone-related headaches also. Timezones can be represented textually as well, simply by a -XX expression on the string. For example, the timezone-less string 2014-08-08 02:11:21.215428 could represent a timezone via the string 2014-08-08 02:11:21.215428-04.
As @MadProgrammer mentioned in a comment below, Postgres also has a timestamp type with timezones; I just happened to choose ones without for the above example.
2 Comments
TimeStamp with timezone type...You could always maintain the data without time zone information to start with and convert it to local time (with time zone) once you have it in your program, but it all comes down to context
Timestampis not the same as aDate,Timestampcontains both date and time information where asDatecontains only date information, as far as PosgreSQL is concerned.Timestamp. As far as I can recall, a pgTimestampshould map tojava.sql.Timestamp