Solved

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

Posted on 2014-02-13
4
516 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 110

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 110

Accepted Solution

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

688 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