🐛 Bug: create a date field, but the database corresponding type is varchar

[bfansheng]

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Steps to reproduce ?

db: mysql 5.7.33
version: 0.109.7

1. create a date field
   
2. but in the database, the type is varchar
   

Desired Behavior

in the database, the type is also date

Project Details

Node: v16.20.1
Arch: x64
Platform: linux
Docker: true
RootDB: sqlite3
PackageVersion: 0.109.7

Attachments

No response

[wingkwong]

Some notes:

• changing to date will result in a difference of 1 day between UI & DB
• need to check with the timezone

[sheldon-welinga]

Some notes:

• changing to date will result in a difference of 1 day between UI & DB
• need to check with the timezone

@wingkwong could you assist with steps to follow to reproduce the day difference issue

[wingkwong]

@sheldon-welinga you can do the following

• create a date field, double check show more to change the type to date
• input a record (choose today, e.g. 2023-09-07)
• in nocodb, it shows 2023-09-07 while in DB the value stores as 2023-09-06

UPD: I think the value is correct. I was using DBeaver with UTC timezone while the UI renders in my local timezone. If I use varchar, then it shows as it is. If I use date, then the value is stored in UTC.

For this issue, I think we can either

• deprecate other types
• or keep the existing types but setting date by default