0

All these different date types confuse me. There's datetime, timestamp, date, and who knows what else. If I need to keep track of a date but would also like to pull queries that says "find records between 06/01/2010 and 06/30/2010," which field type should I be using? At the moment, I'm using "date" because I have no need for a timestamp or time itself.

I think postgresql only has one field type for a date, but it can be formatted via a SQL function I believe. :-)

sdot257
  • 10,046
  • 26
  • 88
  • 122

4 Answers4

1

Date is correct for what you want.

Why so many options? Well, a database could just have 1 type, let's take BLOB - but it wouldn't be very useful. It's nicer if your system knows what kind of data it's handling, so it can offer you special functions (like, as you said, finding dates between given dates).

The different types:

  • DATE is just a date, no time included
  • DATETIME is a date with a time
  • TIMESTAMP is a date with a time, and is automatically changed every time the row is updated.
Konerak
  • 39,272
  • 12
  • 98
  • 118
  • When I first started programming with mysql, I came across an article that said I needed either timestamp or datetime to find ranges between two dates. Is this true or will DATE work just find? – sdot257 Jun 16 '11 at 21:04
  • DATE will work just fine for date-arithmetic or BETWEEN searches. A DATE is just a special DATETIME with the TIME as 0:00:00, takes less bytes... – Konerak Jun 16 '11 at 22:02
1

The difference between DATE and DATETIME/TIMESTAMP should be clear enough - if you don't need time info, use DATE. As for DATETIME vs. TIMESTAMP, there is a distinction: Should I use field 'datetime' or 'timestamp'?

Community
  • 1
  • 1
ceejayoz
  • 176,543
  • 40
  • 303
  • 368
0

Date will work fine for the example you give. If you think you may ever need time in the future, you obviously won't have that information available, and datetime will also work for your purposes, but if you're certain, no reason not to use date.

kinakuta
  • 9,029
  • 1
  • 39
  • 48
0

If you just want to have a date and no timestamp just use DATE.

If you want to know pick between TIMESTAMP and DATETIME. Should I use field 'datetime' or 'timestamp'? answers it

Community
  • 1
  • 1
parapura rajkumar
  • 24,045
  • 1
  • 55
  • 85