MySQL Forums
Forum List  »  General

Error when writing using date aggregate
Posted by: Judah Wright
Date: April 28, 2020 10:25AM

I am having a bit of an issue writing data using data from an aggregate date function.

Consider these two queries:

```
SELECT
session_uuid,
website_uuid,
null,
MIN(created_at),
MAX(updated_at),
MAX(deleted_at)
FROM table1
GROUP BY session_uuid, website_uuid;
```
and
```
SELECT a.session_uuid, MIN(a.created_at) as created_at, MAX(a.updated_at) as updated_at
FROM table1 a
RIGHT JOIN table2 s2 on a.session_uuid = s2.uuid
WHERE s2.created_at IS NULL
GROUP BY a.session_uuid
```

Both of these work fine, 100%, give me data I expect. In addition, all of the date columns have a value (column is nullable, but no null or zero-date valus).

However, both of the following queries give me the following error:
[22001][1292] Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column 'some_column' at row 1

```
UPDATE table2 s
LEFT JOIN (
SELECT a.session_uuid, MIN(a.created_at) as created_at, MAX(a.updated_at) as updated_at
FROM table1 a
RIGHT JOIN table2 s2 on a.session_uuid = s2.uuid
WHERE s2.created_at IS NULL
GROUP BY a.session_uuid
) a on s.uuid = a.session_uuid
SET s.created_at = a.created_at, s.updated_at = a.updated_at
WHERE s.created_at IS NULL;
```
and
```
INSERT INTO table2 (uuid, website_uuid, metadata, created_at, updated_at, deleted_at)
SELECT
session_uuid,
website_uuid,
null,
MIN(created_at),
MAX(updated_at),
MAX(deleted_at)
FROM table1
GROUP BY session_uuid, website_uuid;
```

Any pointers? How would I continue to troubleshoot? Seeing this issue once I could pass off as "I must be doing something wrong," but seeing it twice raises some concerns.

Options: ReplyQuote


Subject
Written By
Posted
Error when writing using date aggregate
April 28, 2020 10:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.