?
Solved

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

Posted on 2014-02-13
4
Medium Priority
?
518 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 

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
 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

771 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