Solved

how to save date time in mysql

Posted on 2014-10-03
16
336 Views
Last Modified: 2014-10-03
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
0
Comment
Question by:Johnny
  • 8
  • 3
  • 3
  • +1
16 Comments
 
LVL 21

Assisted Solution

by:mcsween
mcsween earned 100 total points
ID: 40359944
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
 
LVL 22

Accepted Solution

by:
Kim Walker earned 300 total points
ID: 40359954
The actual data type is DATETIME rather than DATE. It should accept it as presented in your example without any conversion.
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 40359959
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
 

Author Comment

by:Johnny
ID: 40359966
@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
 
LVL 22

Expert Comment

by:Kim Walker
ID: 40359974
According to MySQL documentation, '2012-12-31 11:30:45' and '2012-12-31T11:30:45' are equivalent.
0
 

Author Comment

by:Johnny
ID: 40359977
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
 

Author Comment

by:Johnny
ID: 40359987
@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
 
LVL 21

Expert Comment

by:mcsween
ID: 40359988
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Johnny
ID: 40359990
my values currently saving are like this (snip)
'Closing', '2014-10-03T14:18:16',

Open in new window

0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 40359993
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40359994
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
 

Author Comment

by:Johnny
ID: 40360001
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
 

Author Comment

by:Johnny
ID: 40360005
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
 
LVL 21

Expert Comment

by:mcsween
ID: 40360015
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
 

Author Comment

by:Johnny
ID: 40360022
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
 

Author Closing Comment

by:Johnny
ID: 40360031
went with share of  points to be fair.
thanks again for chiming in and all the posts replies.
cleared that up nicely
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now