Creating new table where timestamps have the same day

user_11653374 0
Viewed 52

I have a database including time column that saves time as timestamps in a string format like '2020-10-19T04:49:15.651867Z' with Z at the end. I want to select timestamps with the same day and put them into a new table. I have tried the following:

CREATE TABLE new_table AS (SELECT * FROM old_table WHERE DAY(time) = 20)

and I get ERROR 1292 (22007): Truncated incorrect datetime value: '2020-10-19T04:49:15.651867Z'.

I thought the problem is with DAY(time) = 20 but the following code works well and shows all entries with the same day:

 SELECT * FROM old_table WHERE DAY(time) = '20';

Can you please help me?

mysql sql
2 Answers

Try:

CREATE TABLE new_table AS     
(SELECT * FROM old_table WHERE DAY(STR_TO_DATE(time, '%Y-%m-%dT%T.%fZ')) = 20)

e.g. Using your example,

SELECT DAY(STR_TO_DATE('2020-10-19T04:49:15.651867Z', '%Y-%m-%dT%T.%fZ')) ==> 19
user_96013100

First, you should not be storing date/time values as strings. So, I recommend that you fix your data model.

But, if you happen to have data in that format, you can use string functions:

where time like '%-20T%'

Or if you really want data from the same date and not day of the month, then:

where time like '2020-10-20%'

I emphasize that this is only appropriate because time is stored as a string, not a proper date/time. If the column were stored using a correct type, then you would use date/time functions.

user_11440350
Related