how to save date time in mysql

i have a date as 2014-10-03T14:18:16.
how do i need to have the mysql (in myphpadmin structure) so that i can use the date/time?
i read that datetime is bad to use. and i get conflicting ways when i look it up via google search.
right now i have DATE with 0000-00-00 as default so it does not save the time. i need the time to be set.
thank you in advance for any code or help you may provide
Johnny
JohnnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mcsweenSr. Network AdministratorCommented:
I've never heard of not using a datetime field in mysql.  Possibly you are confusing with timestamp which should be used to track record changes?  

I personally would use a datetime field in the database and set it with the NOW() function
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now
0
Kim WalkerWeb Programmer/TechnicianCommented:
The actual data type is DATETIME rather than DATE. It should accept it as presented in your example without any conversion.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
I use DATETIME columns all the time so I don't know what you have been reading.  The best format is what you using minus the 'T': 2014-10-03 14:18:16  As @mcsween suggests, I use NOW() to set the DATETIME when I'm inserting values that need the current DATETIME.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

JohnnyAuthor Commented:
@mcsween thanks for the reply,
well my problem is this
i have the string of 2014-10-03T14:18:16 its being pulled from a rets server(like mysql server but a bit different commands)
 i thought of doing last_updated field as i do with a lot of my database setups, and have mysql handle the stamp.
but i have this string thats in there database and it tells me the actual date/time the record was updated its on GMT time and int hat format, i am saving it to the database now but looking at it i didnt do the time part, and i didnt find in Google search on how to properly save that string, if i should convert it or not so i can use its time part later on. hence this post how to properly save the string 2014-10-03T14:18:16 to the database.
heres the post i found on why datetime is bad
http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/
0
Kim WalkerWeb Programmer/TechnicianCommented:
According to MySQL documentation, '2012-12-31 11:30:45' and '2012-12-31T11:30:45' are equivalent.
0
JohnnyAuthor Commented:
so should i just convert it to
$record_datetime = date('Y-m-d H:i:s',strtotime($RECORDMODDATE));

Open in new window

and have default set in structure as '0000-00-00 00:00:00'

?????? is this the right way to do this?
0
JohnnyAuthor Commented:
@xmediaman
OH wait didnt see that
xmediaman2014-10-03 at 11:57:10ID: 40359954
The actual data type is DATETIME rather than DATE. It should accept it as presented in your example without any conversion.

so just change the current DATE to DATETIMe and have as default  '0000-00-00 00:00:00' in my structure and i should be ok???
0
mcsweenSr. Network AdministratorCommented:
I don't agree with the author's opinion in that article.  His real issue is timezones which is easily overcome by using UTC_TIMESTAMP().  If everyone is connecting to the DB from the same timezone I would stick with NOW() which keeps you from having to do math every time you pull a datetime from the db.

He is also overlooking all the additional date/time functions available to you in MySQL that you would loose if using his method.  Now you've got to write your own functions if you want to split, add/subtract, etc with that datetime.

With the functions listed below you can do just about anything you need with that field; assuming you store it in the format MySQL expects.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
0
JohnnyAuthor Commented:
my values currently saving are like this (snip)
'Closing', '2014-10-03T14:18:16',

Open in new window

0
Kim WalkerWeb Programmer/TechnicianCommented:
As I said, you should not have to convert anything. My SQL should recognize the value with the "T" in the middle. You can set the default to 0-0-0 0:0:0 if you wish, it'll convert that to 0000-00-00 00:00:00.
0
Dave BaldwinFixer of ProblemsCommented:
That article makes some good points but in general, I don't agree with his conclusion.  The most common reason for recording the DATETIME of an entry is to keep them in the order that they occurred.  Any consistent method is just fine and DATETIME is more readable than TIMESTAMP.
0
JohnnyAuthor Commented:
well thanks for the chim ins...and glad we cleared it up some.
im going ot go with changing the date to date time and default structure.


** now how to accept this for an answer hrrrmmm
0
JohnnyAuthor Commented:
im going to accept xmediaman post ID: 40359954 as answer as it was the first and what i went with if thats ok with everyone?
0
mcsweenSr. Network AdministratorCommented:
I don't agree with that; I was the first poster and the question was asking about using a datetime field, not about conversion.  I think there should be some splitting of points as we all helped you with this question.
0
JohnnyAuthor Commented:
although most of the posts are opinion or confirmation - and post  ID: 40359954 is actual answer hows awarding 100 points to support and answer for 300?
trying to be fair.
0
JohnnyAuthor Commented:
went with share of  points to be fair.
thanks again for chiming in and all the posts replies.
cleared that up nicely
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.