3

i would like to store the date and time in the following format in the database

a) DD-MM-YYYY or i could add up HH-MM b) as my servers are located in US i would like to fetch the time as per IST. now that will be GMT : +5:30

earlier i used to store the date in mysql in this format timestamp(int(11)):1291746600. i used to convert the date with strtotime(); currently my intention of storing the date is just to store and display. in the future i would like to calculate the no. of days, months etc.

which would be the best possible solution for this?

P:S : i would appreciate if someone could explain me which datatype to use and how to use it with PHP.

Ibrahim Azhar Armar
  • 25,288
  • 35
  • 131
  • 207

3 Answers3

10

Use DATETIME fields! They are the best format to store dates in mySQL. They offer proper indexing and optimization, and you can use the full range of mySQL's date functions.

Any specific format you need to output the fields in, you can create from a DATETIME field using DATE_FORMAT().

MySQL doesn't support time zones in DATETIME fields - you will usually set a global time zone on the server and use that.

There's good related reading on Timezones in these questions:

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
3

Use a datetime field.

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

You will have access to a lot of function for date manipulation.

If you want still to use a varchar use the ISO-TIME format (YYYY-MM-DD) not the us.

VGE
  • 4,171
  • 18
  • 17
3

I think you should use DATETIME data type. For more operations on date and time have a look at functions listed here http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Tasawer Khan
  • 5,994
  • 7
  • 46
  • 69