Solved

how to save date time in mysql

Posted on 2014-10-03
16
334 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 21

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 82

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 21

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 21

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 82

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

17 Experts available now in Live!

Get 1:1 Help Now