Solved

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

Posted on 2014-04-16
9
316 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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