Solved

how to save date time in mysql

Posted on 2014-10-03
16
338 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 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