Solved

inserting rows from one table into another; where dateAgo is varchar or datetime

Posted on 2014-04-16
9
311 Views
Last Modified: 2014-05-05
CREATE TABLE `a_messages` (
  `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` datetime 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=3952 DEFAULT CHARSET=utf8;





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;



I want to insert all values of a_messages2 into a_messages
if message_id is the same, then do not insert (because I prefer the values in a_messages and do not want duplicate rows with same message_id

note: a_messages2.dateAgo is varchar and a_messages.dateAgo is datetime


these are values of a_messages.dateAgo
just now
just now
just now
just now
(17 mins ago)
(07/25 11:26)
(07/25 11:20)
(07/25 11:19)
2 days ago
1 week ago
2 weeks ago
(12 hrs ago)
(06/26 12:54)
(07/25 09:58)
(14 hrs ago)
(07/25 11:01)
(07/25 10:59)
(07/25 10:24)
(07/25 10:23)
(07/25 09:48)
(16 hrs ago)
(20 hrs ago)
(21 hrs ago)
(07/25 22:27)
(07/25 09:43)
(07/26 12:44)
(07/26 12:17)
(07/26 11:06)
(07/26 07:07)
(07/24 17:47)
(07/24 10:45)
(07/24 08:31)
(07/23 06:47)
(07/23 06:35)
(07/23 06:34)
4 days ago
5 days ago
6 days ago
3 weeks ago
1 month ago
3 days ago
1 day ago
(11 mins ago)
(21 mins ago)
(25 mins ago)
(15 mins ago)
(19 mins ago)
(16 mins ago)
(18 mins ago)
(20 mins ago)
(08/06 19:48)
(22 mins ago)
(23 mins ago)
(24 mins ago)
(26 mins ago)
(27 mins ago)
(08/07 06:06)
(08/06 23:33)
(08/06 20:27)
(08/06 18:08)
(08/06 14:42)
(08/06 14:41)
(08/06 10:14)
(08/05 15:42)
(08/05 15:33)
(08/01 10:11)
(08/04 05:57)
(08/04 04:21)
(08/03 17:08)
(08/03 16:40)
(08/01 10:15)
(10 mins ago)
(13 mins ago)
(14 mins ago)
(42 mins ago)
(1 hr ago)
(22 hrs ago)
(08/07 07:37)
(08/07 07:32)
(08/07 07:29)
(08/07 07:20)
(11 hrs ago)
(7 hrs ago)
(08/05 16:57)
(08/05 13:59)
(08/05 12:34)
(08/05 10:49)
(08/04 15:49)
(08/04 13:02)
(08/04 12:12)
(08/04 11:55)
(08/04 04:48)
(08/04 13:08)
(08/04 12:11)
(08/04 12:08)
(08/04 02:31)
(08/04 15:59)
(08/04 15:58)
(8 hrs ago)
(9 hrs ago)
(10 hrs ago)
(09/03 11:45)
2013-10-23 14:08:00
2013-10-23 14:04:00
2013-10-23 13:54:00
2013-10-23 13:35:00
2013-10-22 12:37:00
2013-10-22 10:31:00
2013-11-30 08:31:00

Open in new window



SELECT dateAgo
FROM a_messages2
WHERE dateAgo
RLIKE  '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$';


I still want the a_messages2.dateAgo varchar values

so my suggestion, or maybe you have a better suggestion

create a new column a_messages.dateAgo_varchar

and all the a_messages2.dateAgo that are not in the proper mysql datetime format insert into a_messages.dateAgo_varchar, else insert into a_messages.dateAgo
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
  • 6
  • 3
9 Comments
 
LVL 11

Expert Comment

by:Murfur
ID: 40004003
The obvious thing is to convert the VARCHAR to DATETIME so that the two match format but exactly how you do it rather depends on how the VARCHAR date/time string is formatted and what exactly is stored there.

Your example data above shows values like 'Just Now' and '2 days ago' but presumably those are a UI interpretation of a real date time value relative to the current date/time otherwise 'Just Now' would still be the same in three days time which would no longer be true.

So you just have to tell the conversion process the format of the incoming data e.g. if your VARCHAR date is stored as 16 April 2014 15:25:17 then you might do:
STR_TO_DATE( '16 April 2014 15:25:17', '%d %M %Y %H:%i:%s' )

Open in new window


So in the VALUES of your INSERT you might do:
...message_id, message_text,STR_TO_DATE( dateAgo, '%d %M %Y %H:%i:%s' ), message_read...

Open in new window


See these references for further details:
http://www.w3schools.com/sql/func_date_format.asp
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
0
 

Author Comment

by:rgb192
ID: 40004089
SELECT dateAgo
FROM a_messages2
WHERE dateAgo
RLIKE  '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$';


is the regular expression.

This question is only mysql because the data does not link to php
I want to store fields with words in dateAgo_varchar column or a similar column
But php is another experts-exchange question

This question is just the first part; combining with mysql
0
 

Author Comment

by:rgb192
ID: 40006388
The obvious thing is to convert the VARCHAR to DATETIME so that the two match format
Yes. But that is another question using php.  This question is copying values to dateAgo_varchar (varchar).
My goal with this question is to have one table.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 11

Accepted Solution

by:
Murfur earned 500 total points
ID: 40006541
No, that was an SQL conversion statement, nothing to do with PHP...
Basically you need to convert the string into a datetime format otherwise you just get 0000-00-00 00:00:00 as the value after insert.

INSERT INTO `a_messages` (
	`a_messages_id`,
	`conversation_id`,
	`profile_id`,
	`sender`,
	`message_id`,
	`message_text`,
	`dateAgo`,
	`message_read`,
	`this_user`
) SELECT
	`a_messages_id`,
	`conversation_id`,
	`profile_id`,
	`sender`,
	`message_id`,
	`message_text`,
	STR_TO_DATE(`dateAgo`, '%d %M %Y %H:%i:%s'),
	`message_read`,
	`this_user`
FROM
	`a_messages2` m2
WHERE
	m2.`message_id` NOT IN (
		SELECT DISTINCT
			m.`message_id`
		FROM
			`a_messages` m
	)

Open in new window

0
 

Author Comment

by:rgb192
ID: 40012798
I have created a new column in a_messages
 a_messages.dateAgo_varchar

and there is a regex included in the first question.



maybe use an case statement to put the values with the word 'ago' which are not dates and will not work for
STR_TO_DATE(`dateAgo`, '%d %M %Y %H:%i:%s'),



because

WHERE
      m2.`message_id` NOT IN (
            SELECT DISTINCT
                  m.`message_id`
            FROM
                  `a_messages` m
      )


will not copy the value to the new column
 a_messages.dateAgo_varchar
0
 
LVL 11

Expert Comment

by:Murfur
ID: 40020262
That's because you haven't changed the STR_TO_DATE parameters to match the structure of your existing varchar date time format in table a_messages2

For example:
SELECT dateAgo AS `varchar`, STR_TO_DATE( dateAgo, '%Y-%m-%d %H:%i:%s' ) AS `datetime` 
FROM a_messages2
WHERE dateAgo
RLIKE  '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$';

Open in new window

0
 

Author Comment

by:rgb192
ID: 40021869
could you modify query to have
'case statement'
'or statement'

to add to dateAgo_varchar if in wrong format so I can test better


also note that dateAgo
(07/25 22:27)
has
(
)

which may not be covered by str_to_date
0
 

Author Comment

by:rgb192
ID: 40042815
how can you modify this query
http://www.experts-exchange.com/viewCodeSnippet.jsp?refID=40006541&rtid=20&icsi=1

to insert the dateAgo varchar values
0
 

Author Closing Comment

by:rgb192
ID: 40043564
I need to create a new column dateAgoVarchar(varchar) before I do insert

so this insert could work

thanks.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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