Solved

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

Posted on 2014-04-16
9
305 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 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