?
Solved

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

Posted on 2014-04-16
9
Medium Priority
?
355 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
  • 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
 
LVL 1

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
 
LVL 1

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 11

Accepted Solution

by:
Murfur earned 2000 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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

590 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