Solved

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

Posted on 2014-04-16
9
299 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Accepted Solution

by:
Murfur earned 500 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:rgb192
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I need to create a new column dateAgoVarchar(varchar) before I do insert

so this insert could work

thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now