bash mysql 24hr time conversion

Posted on 2014-08-01
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
    LVL 107

    Expert Comment

    by:Ray Paseur
    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

    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 107

    Accepted Solution

    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.

    Author Comment

    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 107

    Expert Comment

    by:Ray Paseur
    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 107

    Expert Comment

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

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    733 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