0

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

2
  • 2
    Becareful, Timestamp is not the same as a Date, Timestamp contains both date and time information where as Date contains only date information, as far as PosgreSQL is concerned. Timestamp. As far as I can recall, a pg Timestamp should map to java.sql.Timestamp Commented Aug 8, 2014 at 6:02
  • Thank you. That is my concern. Commented Aug 8, 2014 at 6:16

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for your advise. I am also thinking to represent it as a String. However, I dont really have solid idea how to deal with different timezones so that I think postgresql Timestamp will save some extra steps.
@user3819470 If you need to manage time zones, postgresql has a 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.