How to save birthday date in PostgreSQL?

Question

I am doing a GraphQL tutorial, and I need to save user's birthday. I am using TypeORM, Apollo-server-express and PostgreSQL.

These are my current user entity and schema files. I don't know how to save/input birthday, so I made it nullable for now.

// user.entity.ts

@Entity()
export class User extends BaseEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column('text')
  first_name: string;

  @Column('text')
  last_name: string;

  @Column({
    unique: true
  })
  @Length(5, 100)
  @IsEmail()
  email: string;

  @Column()
  password: string;

  @Column({
    nullable: true
  })
  birthday: Date;
}

// user.schmea.ts

  type User {
    id: ID!
    first_name: String
    last_name: String!
    email: String!
    birthday: Date
    created_at: Date @date(format: "HH:MM mmmm d, yyyy")
    updated_at: Date @date(format: "HH:MM mmmm d, yyyy")
  }

  input CreateUserInput {
    first_name: String
    last_name: String!
    email: String!
    password: String!
    birthday: Date
  }

This is my example mutation input for createUser:

mutation {
  createUser(data: {
    first_name: "Some",
    last_name: "One",
    email: "some@one.com",
    password: "hashThisPlease1"
  }) {
    id
    first_name
    last_name
    email
  }
}

Should I input birthday as a string like "1990-12-30"? Is it the standard way to save birthday column in PostgreSQL?

Answer 1

I'm by no means an expert, but I would agree with JudgeFudge. Though I like to add, that you have the option to set the property type to "string" like that:

@Column({ type: "date" })
date_of_birth: string;

Then you'll get the (in my opinion) better understandable error message:

"Datum/Zeit-Feldwert ist außerhalb des gültigen Bereichs: »1986-20-25«" 
("Date/Time field value is not in valid range")

instead of:

"ungültige Eingabesyntax für Typ date: »NaN-NaN-NaN«" 
("invalid input syntax for type date")

And if you input: "1986-02-30" with property type of "string" you'll get an error like above, while with property type of "Date" it will convert it to "1986-03-02" due to the javascript Date constructor.

But you can loose some "schema transparency", since you're asking for a value of type "string" rather than "Date", when for example working with TypeGraphQl like I do and implementing entity-partials in the Args/Input-Types.

Coming to an end, if your requests contain a stringfied (to JSON) instance of an DateObject, an invalid date would be caught when trying to initialize this instance with the DateObject constructor, thus you can work with "Date" instead of "string", when using this (last) approach.

Answer 2

A birthday is typically saved as date and most databases (including Postgres) are supporting dates (see docs). All you need to do is change the birthday decorator to:

  @Column({
    type: 'date',
    nullable: true
  })
  birthday: Date | null;