2

I am creating a table which has create_date and modify_date field in mysql, but I am struggling in decide which data type is the best choice for these 2 fields. Should i use date time for both fields, or should i use date time for create_date and time stamp for modify_date ?

ale
  • 11,636
  • 27
  • 92
  • 149
Huy4NG
  • 29
  • 1
  • 9

3 Answers3

1

Depends on your requirements. If you want to know the exact time these things happened then use a date/time (DATETIME in MySQL). Or, if you're happy with with just the date then choose date (DATE in MySQL)! If you don't know when maybe use DATETIME just to be safe (so you have the precision if you need it).

You're using MySQL so here is the documentation: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

ale
  • 11,636
  • 27
  • 92
  • 149
  • yes, i want to know the exact time and all the date information for both field. – Huy4NG Aug 06 '13 at 16:09
  • Well if you want to know the exact time then use DATETIME in favour of DATE. Note also that you might want to consider the difference between TIMESTAMP and DATETIME: http://stackoverflow.com/questions/409286/datetime-vs-timestamp – ale Aug 06 '13 at 16:10
0

I'm assuming you're talking about a database table. If that is the case and you need to know date information choose a date time for both modify_date and create_date. If you don't need date information use a time stamp.

Aaron
  • 7,055
  • 2
  • 38
  • 53
  • yes, i want to know all the date information for these two fields – Huy4NG Aug 06 '13 at 16:09
  • Then use a Date Time field for both. Easy! What is your hesitation with using the Date Time over the Time Stamp? – Aaron Aug 06 '13 at 16:09
  • these two data type have the same format, right? but i just saw a question about "date time Vs timestame"?. And the asnwer said Timestamps used to track changes to records. and datetime is used to store a specific value? – Huy4NG Aug 06 '13 at 16:19
  • Your answer depends on your usage. Can you provide more information on what you're trying to accomplish? If you're just recording the dates on which your records are created and updated Date Time should be just fine. – Aaron Aug 06 '13 at 16:24
  • i want to track the time, when a record in the table has updated just like the answer of the "date time vs timestamp?" quetion – Huy4NG Aug 06 '13 at 16:34
  • Right, so if you want the database do this automatically for you that is a different question, if you want to update the value yourself a Date Time will always store the time that you enter, you just have to provide it yourself. – Aaron Aug 06 '13 at 16:50
  • oh i always let the database do this automatically for me, and i use my laptop not the server time becos of the time different zone – Huy4NG Aug 06 '13 at 17:34
  • so if i want the database do it automatically for me, I should use Time Stamp, elseif I want to update the value myself, then I should use Date Time – Huy4NG Aug 06 '13 at 17:43
0

Sorry I'm from Ukraine and I use Google Translate. It all depends on how you are going to use them, if you take away from each other then TimeStamp, if just to show (for example in the news), then it is better Date Time. Generally I recommend TimeStamp

Norbrecht
  • 71
  • 3
  • 11
  • so, what is the time range for time stamp? – Huy4NG Aug 06 '13 at 16:11
  • The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. All these things are in the docs: http://dev.mysql.com/doc/refman/5.0/en/datetime.html – ale Aug 06 '13 at 16:12
  • yea, i already see that one. so, is that mean timestamp life is can live until 2038 or it can support for less than 70years ? – Huy4NG Aug 06 '13 at 16:23