Best data type for PostgreSQL datatime type

Question

Actually I have:

CREATE TABLE public.user(
  id BIGSERIAL NOT NULL,
  nick VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  born DATE,
  joined DATE,
  tags text[]
);

I want to choose best option to save register time, and born data. I searched internet but didnt find any good results. Maybe its better to use time from python, and just insert it to postgres?

Answer 1

For the birth date you don't need the time of the day, so a DATE would be appropriate.

For the registration date you probably would like to save the date and time. In this case TIMESTAMP would be more appropriate.

Avoid using VARCHAR to store dates. Since it doesn't validate anything, it will allow you to store bad information and you won't be able to compute any information on them easily. For example:

• Bad string value #1: 35-jan-2017: this date does not exist.
• Bad string value #2: 10-12-99: is it 10 of December, or 12 of October?