Mysql View : Issue when Providing userdefined datatype in creating VIEW with if statement

Question

 desc user_roles;
+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| id         | int(50)    | NO   | PRI | NULL              | auto_increment              |
| user_id    | int(50)    | NO   | MUL | NULL              |                             |
| role_id    | int(50)    | NO   |     | NULL              |                             |
| created_at | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status     | tinyint(3) | YES  |     | 1                 |                             |

create or replace view bff_user_roles as select user_roles.id as id,  
user_id as user_id, role_id as role_id, updated_at as updated_at, status as status,
IF(created_at ='0000-00-00 00:00:00', "1990-01-01 23:59:59", created_at)
 as created_at as timestamp from user_roles;

desc bff_user_roles;
+------------+-------------+------+-----+---------------------+-------+
| Field      | Type        | Null | Key | Default             | Extra |
+------------+-------------+------+-----+---------------------+-------+
| id         | int(50)     | NO   |     | 0                   |       |
| user_id    | int(50)     | NO   |     | NULL                |       |
| role_id    | int(50)     | NO   |     | NULL                |       |
| updated_at | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
| status     | tinyint(3)  | YES  |     | 1                   |       |
| created_at | varchar(19) | NO   |     |                     |       |
+------------+-------------+------+-----+---------------------+-------+

The issue is here that when I go to create VIEW in MySql db then it is taking created_at column datatype as varchar . But it should be and required timestamp. I have tried to add explictly datatype but it is not working. I have taken if statment in VIEW due some legacy data issue in user_roles table.

Answer 1

IF(created_at ='0000-00-00 00:00:00', "1990-01-01 23:59:59", created_at) deals with 2 alternative values. One of them (created_at) is TIMESTAMP, and another one ("1990-01-01 23:59:59") is VARCHAR(19). Server selects the most common format from those two, and it is VARCHAR(19).

You must use not string literal but datetime literal:

IF(created_at ='0000-00-00 00:00:00', TIMESTAMP '1990-01-01 23:59:59', created_at)

In this case the most common datatype will be DATETIME.