bash mysql 24hr time conversion

In a bash script, we have the following which is being used for time stamps;

TIME="$(date +"%F %T")"

The above is being stored in the following mysql field;

Field name    	Type    	Allow nulls?    	Key    	Default value    	Extras   
	id 	int(11) 	No 	Primary 	NULL 	auto_increment
	time 	timestamp 	No 	None 	CURRENT_TIMESTAMP 	on update CURRENT_TIMESTAMP

Open in new window


First, what do I need to change in the script to in order to start sending 24hr clock format instead?

Second, is there any way of converting the data already stored in the database from 12hr format to 24hr format?

If you reply, please understand that I am not a database person so will need actual instructions or a script or what ever the solution might be.

Thank you.
projectsAsked:
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.

Ray PaseurCommented:
How to use DATETIME values in PHP and MySQL:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

You probably want to change to using DATETIME for the column definition.   The TIMESTAMP definition causes automatic updating whenever the row is affected by a query.

It is quite likely that the existing data in the table is already 24-hour format.
0
projectsAuthor Commented:
From what I can see, the script is already sending in 24hr format as you say.
Yet the programmer is telling me that we need the time in 24hr format.

In the DB, I can clearly see 24hr format.

	id 	time
	132751 	2014-07-31 23:59:15
	132752 	2014-07-31 23:59:21
	132753 	2014-07-31 23:59:31
	132754 	2014-07-31 23:59:35
	132755 	2014-07-31 23:59:55
	132756 	2014-08-01 00:00:03
	132757 	2014-08-01 00:00:08

Open in new window


I either posted this question too quickly or the programmer didn't explain something correctly. I will have to wait to hear back from him at this point.
0
Ray PaseurCommented:
There are a lot of moving parts between the database and what you might see once the data is visualized.  There may be some intermediate formatting going on.  All of your date/time information should be held internally in the ISO-8601 format, with format conversion occurring immediately before display, and with the formatted values never reused in the code. It's all in the article.

Please be clear on the concept of the difference between DATETIME and TIMESTAMP columns.  You have to send data to the database in order to populate the DATETIME values.  The TIMESTAMP values are populated automatically by the database engine.
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
Upgrade your Question Security!

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

projectsAuthor Commented:
I wonder why none of the programmers have caught this???
So the TIMESTAMP type is actually from the database itself and not from the sender, even though the data is being sent? Does this mean it's being lost as well then?

So for actual time stamps, I need to be using DATETIME?
0
Ray PaseurCommented:
TIMESTAMP type is actually from the database itself and not from the sender, even though the data is being sent? Does this mean it's being lost as well then?
I don't know for sure.  You might set up a test if you're curious.  I've never tried doing this before.  I just read the man page and followed the advice that seemed to suggest the DATETIME was most useful for storing my information and the TIMESTAMP was most useful for automatic population.
http://dev.mysql.com/doc/refman/5.1/en/datetime.html
0
Ray PaseurCommented:
Thanks for the points and thanks for using E-E, ~Ray
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.