1

For my new database i want to save a given date and time and year from php to my database with mysqli, in sql i can make a field :

DATE

DATETIME

TIMESTAMP

TIME

YEAR

I'm using the database only with php, what type should i select? and for what reason.

I think datetime and timestamp are the best options. but cant find any reason why 1 should be better then the other. Can someone help me to chose ?

Or is it better to save date time and year separate?

I want to make querys to get values from last week etc.

Sven van den Boogaart
  • 11,833
  • 21
  • 86
  • 169
  • Depends on what you're going to use. If you're storing a date, don't use a `TIMESTAMP`. If you're storing a timestamp, don't use a `DATE`. If you don't need time, don't use `DATETIME`. – Amal Murali Nov 28 '13 at 15:36
  • I personally use datetime for user specified / variable dates and timestamp with store current on insert enable when I just need to store the now() time on record insert. They're all much of a muchness though I just personally find datetime/timestamps easier to deal with – Dave Nov 28 '13 at 15:36
  • i want to store the date time and year of an event . – Sven van den Boogaart Nov 28 '13 at 15:37
  • the first timestamp field in a table will auto-update anytime you modify the record. maybe that's what you want, but if you're storing (say) HR records, you don't want someone's birthday to always be "now". – Marc B Nov 28 '13 at 15:37
  • See that discussion http://stackoverflow.com/questions/409286/datetime-vs-timestamp – LevB Nov 28 '13 at 15:37
  • I want to store something like a birthday so not the current date – Sven van den Boogaart Nov 28 '13 at 15:38

1 Answers1

3

For my new database i want to save a given date and time and year from php to my database

So, store it as DATETIME. It includes the year.

Here is how look at those types:

  • DATE: use for dates (with years), for example a a birthdate ("2010-11-23")

  • TIME: use for a time in a day, for example the start of your lunch ("12:00")

  • DATETIME: use for a specific date and time, for example the start of a meeting ("2010-11-23 12:00")

  • TIMESTAMP: use for when a specific thing happened, for example the time a certain meeting was created ("1385653500"; this often includes timezone information in its definition)

  • YEAR: use to store a year, for example the start year of a war ("1653")

Note that you can always cast "larger" types to "smaller" types. E.g. you can cast a DATE into a YEAR.

Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195