0

I'm trying to create a new table from my query-results with the following code while having two different date formats (formatted as text in the tweet table) in the same column: 

Create Table tweets_adj
select
t.fullname as company_name,
t.`user` as twitter_user_name,
t.id as tweet_id,
cast(t.likes as unsigned int) as likes,
cast(t.replies as unsigned int) as replies,
cast(t.retweets as unsigned int) as retweets,

case 
    when right(t.`text`,1)="'" and left(t.text, 2) = "b'" then left(replace(t.`text`, "b'", ""), length(replace(t.`text`, "b'", ""))-1)
    when right(t.`text`,1)='"' and left(t.text, 2) = 'b"' then left(replace(t.`text`, 'b"', ''), length(replace(t.`text`, 'b"', ''))-1)    
     else t.`text` end as content,

case 
    when STR_TO_DATE(t.timestamp, '%Y.%m.%d %H:%i:%s') is null
        then STR_TO_DATE(t.timestamp, '%d.%m.%Y %H:%i') 
        else STR_TO_DATE(t.timestamp, '%Y-%m-%d %H:%i:%s') 
end as timestamp,
t.url,
t.hashtags
from tweets t
limit 1000;

I have to sorts of timestamps in the column timestamp: 20.08.2014 20:17 2014-03-10 02:19:02

I want to unify them to one date format (none specific format preferred) and save them in the right format as a date and then export the whole select into a new query.

Unfortunately I get an error:

Incorrect datetime value: '20.08.2014 20:17:00'

I also found this question but I'm too much of a beginner to understand that solution.

table tweets looks like this - all columns are formated as text e.g. fullname TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', (...) timestamp TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', (...)

Any help is so much appreciated - Thanks!!

GreenPirate
  • 37
  • 8
  • 1
    if your tweets.timestamp column is a Timestamp Type, try without using the STR_TO_DATE function and see what you have. The structure of tweets table would help here – Thomas G Apr 30 '18 at 08:41
  • Thanks Thomas! Trying without STR_TO_DATE yields in error 'Operand should contain 1 column(s)' or 'SQL syntax error'. All columns have a simple text structure: CREATE TABLE `tweets` ( `fullname` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', (...) – GreenPirate Apr 30 '18 at 08:56
  • I also edited the question. – GreenPirate Apr 30 '18 at 09:05
  • then STR_TO_DATE(t.timestamp, '%d.%m.%Y %H:%i') %s is missing from this line. Please update and check. – Jyoti mishra Apr 30 '18 at 11:17
  • Thanks @Jyotimishra for your help! Valid point, the %s is missing but I still get an incorrect datetime value... – GreenPirate Apr 30 '18 at 11:39
  • what is your timestamp value? – Jyoti mishra Apr 30 '18 at 11:52
  • not too sure what you mean.. **timestamp from table tweets:** 20.08.2014 20:17 (create table statement (...) `timestamp` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', (...) **timestamp from new table:** 2014-08-20 20:17:00 >>> so the select part of the code itself works just fine, only when I add `Create table tweets_adj` I get the mistake. – GreenPirate Apr 30 '18 at 11:59
  • are you still getting same error ? – Jyoti mishra Apr 30 '18 at 12:10
  • @Jyotimishra yes: I updated my question with the current code. I get the above mentioned results but as soon as I add `create table` I get the incorrect date time value error. – GreenPirate Apr 30 '18 at 12:20
  • Does your database contains any value "20.08.2014 20:17" ? – Jyoti mishra Apr 30 '18 at 12:34
  • yes (you can see the entry in the picture above) – GreenPirate Apr 30 '18 at 12:43
  • Can you please change your value from 20.08.2014 20:17 to 20.08.2014 20:17:00 to check. I think it should work – Jyoti mishra Apr 30 '18 at 12:49
  • I altered the data in the tweet database. I also altered it to 20-08-2014 (...); still getting the same problem. so... I need to make sure that all dates have the same fomat and then create a table out of the adjustet data. Maybe there is another, smarter way? – GreenPirate Apr 30 '18 at 13:00

0 Answers0