0

How can I best store a String with format yyyy-MM-dd (without time declaration) in SQL database (postgres)?

I later want to use that String always as Date type. I also want to execute query against the database to give me records that are before or after that Date.

  • Should I store it as a String or as a Date type in DB?
  • If I store it as a Date, in database I see yyyy-MM-dd HH:mm:ss. How could I prevent the time declaration?
membersound
  • 81,582
  • 193
  • 585
  • 1,120

2 Answers2

7

If you do not want to store a time component, then use the DATE data type. It does not have a time or a time zone component, so is useful for dates of birth, dates of employment start/end, and other data for which the time is not relevant.

http://www.postgresql.org/docs/9.3/static/datatype-datetime.html

The display format is a matter for the application -- just use the correct data type. YYYY-MM-DD is documented as the best format for suplying dates, though.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
2

Always recommended one is Date with time-stamp. If you don't need then while storing store it as 00:00:00.(Use Sql Date for date without time-stamp.)

Use business logical in order to truncate the time and the format you required. Service layer you can play with date and in most of DB its better to store Date with timestamp.

Sureshkumar Panneerselvan
  • 749
  • 6
  • 22
  • +1 D timestamp is good to store datetime. – Sagar Chavan Jan 14 '14 at 09:04
  • 2
    "Date with timestamp"? I don't think that exists in Postgres. If you do have a time component and a time zone specified then you're going to get changes to the time component based on the TimeZone parameter. http://www.postgresql.org/docs/9.3/static/runtime-config-client.html#GUC-TIMEZONE – David Aldridge Jan 14 '14 at 09:13
  • I am talking in Java datatype. Also its better to use java datatypes otherwise need a converter between db and java. – Sureshkumar Panneerselvan Jan 14 '14 at 09:23
  • If your issue is fixed kindly close the question or update the issue. – Sureshkumar Panneerselvan Jan 15 '14 at 08:43
  • Don't agree at all that it's better to store a date with a time component if the time component is not supported by the domain model. Your system should be an accurate representation of your domain. – Chris Harrison Nov 05 '21 at 13:17