[Last Call] Learn how to a build a cloud-first strategyRegister Now


bash mysql 24hr time conversion

Posted on 2014-08-01
Medium Priority
Last Modified: 2014-08-01
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.
Question by:projects
  • 4
  • 2
LVL 111

Expert Comment

by:Ray Paseur
ID: 40234670
How to use DATETIME values in PHP and MySQL:

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.

Author Comment

ID: 40234717
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.
LVL 111

Accepted Solution

Ray Paseur earned 2000 total points
ID: 40234734
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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 40235313
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?
LVL 111

Expert Comment

by:Ray Paseur
ID: 40235380
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.
LVL 111

Expert Comment

by:Ray Paseur
ID: 40235466
Thanks for the points and thanks for using E-E, ~Ray

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 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