Django error: cannot cast type date to time without time zone

Question

Please help me with this error:

File "/Users/ecommerce/proshopvenv/lib/python3.8/site-packages/django/db/backends/utils.py", 
line 84, in _execute

return self.cursor.execute(sql, params)

psycopg2.errors.CannotCoerce: cannot cast type date to time without time zone
LINE 1: ..." ALTER COLUMN "createdAt" TYPE time USING "createdAt"::time


The above exception was the direct cause of the following exception:
"a lot of code, and:"

django.db.utils.ProgrammingError: cannot cast type date to time without time zone
LINE 1: ..." ALTER COLUMN "createdAt" TYPE time USING "createdAt"::time

In models i have:

    createdAt = models.DateTimeField(auto_now_add=True)

and how i understand from docs https://docs.djangoproject.com/en/3.2/ref/models/fields/

"The auto_now and auto_now_add options will always use the date in the default timezone at the moment of creation or update."

But zone doesn't create, or error in something else, i creating new database, so i can delete all tables

Maybe there is step in django where it initials time zone?

Readed this topic postgreSQL alter column data type to timestamp without time zone and many others but can't fix it to 'migrate' without errors

Make sure You have a line in your setting.py which looks like this TIME_ZONE = 'Asia/Kolkata' — Vivek Anand
The issue is this: ALTER COLUMN "createdAt" TYPE time USING "createdAt"::time". Something is trying to alter the type of your createdAt field away from some form of date(I presume) to a time type. That is not what you want. You need to track down what is doing this. — Adrian Klaver
I think there is problem with modifying date to time without time zone — Marat Mag

Belayer Belayer · Accepted Answer · 2021-05-02T20:33:12

Yes there a problem modifying a date to time with or without time zone. The problem being that in Postgres date does not have a time component so any successful attempt to get time results in '00:00:00'. Try

select current_date::timestamp::time;

Given that then assuming your objective is the change the type then just drop and re-add the column;

alter table test_table drop createdat;
alter table test_table add  createdat time  default '00:00:00'::time;

Of course I have never figured out what good time is without the date. Which came first, '10:15:00' or '11:15:00'? Well if 10:15 was today and 11:15 was yesterday then 11:15 came first. Unless you define it as hours-since-midnight. So perhaps a better option:

alter table test_table 
      alter column createdat 
               set data type timestamp 
             using createdat::timestamp;

See Demo Yes, timestamp takes more space, but is much more flexible.

Django error: cannot cast type date to time without time zone

2 Answers