Improve company productivity with a Business Account.Sign Up

x
?
Solved

change date if date is in a different format to correct mysql format

Posted on 2014-02-13
4
Medium Priority
?
525 Views
Last Modified: 2014-02-17
discard column fields with 'ago'

I think an idea was to create a new column 'modified_date' (varchar or datetime)

CREATE TABLE `a_messages2` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` varchar(20) default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9969 DEFAULT CHARSET=utf8;

Open in new window



http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28319753.html#a39756115

if date is a date and date is different that correct mysql time format then change to mysql time format

instead of coming from a file
date comes from the table column
0
Comment
Question by:rgb192
  • 2
  • 2
4 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39858578
Please read this article about handling DATETIME values.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

SQL table columns that would hold DATE or DATETIME values should be defined as DATETIME columns.

If you have dates in your SQL table and you've got them in VARCHAR columns, you can use ALTER TABLE to add a column of the DATETIME type with DEFAULT '0000-00-00 00:00:00'.  Then you can run a script to SELECT the VARCHAR column, use strtotime() and date('c') to convert the value to the ISO-8601 DATETIME string, and UPDATE the new column with the value.  If strtotime() returns FALSE, do not update the new column, but instead flag the row for manual intervention.
0
 
LVL 1

Author Comment

by:rgb192
ID: 39860716
what is a way to implement this function if the values are already in the table
maybe save converted time to another column on the table

	/*
	this function converts date time string such as 1 minute ago, 3 hours ago, 9/17 4:13, just now to mysql time Created by Ray Passeur
	*/
	public function ConvertDateTime($date_time_str = 'now')
	{
		// remove unwanted characters
		$date_time_str = str_replace('(', NULL, $date_time_str);
		$date_time_str = str_replace(')', NULL, $date_time_str);

		// translate to US English date format like 'hour', 'now', etc.
		$date_time_str = str_replace('hr', 'hour', $date_time_str);
		$date_time_str = str_replace('just now', 'now', $date_time_str);

		$date_format = NULL;
		try
		{
			$datetime = date_create($date_time_str);
			$date_format = date_format($datetime, 'Y-m-d H:i:s');
		}
		catch (Exception $e)
		{
			$this->writeLog($e->getMessage(), TRUE);
		}

		return $date_format;
	}

Open in new window

0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39860727
Right, that is what I was suggesting here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28364626.html#a39858578

Set up some test tables and experiment with it a little bit.  You will find the way, I promise!
0
 
LVL 1

Author Closing Comment

by:rgb192
ID: 39865486
Thanks.
There is knowledge in the article.  I can not create a function with output in another column without spending hours in procedural code.  So I have a related question.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28366944.html
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

607 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