Home > database >  How to use Django's DurationField with Postgres, when defined previously using sqlite?
How to use Django's DurationField with Postgres, when defined previously using sqlite?

Time:08-14

I recently moved all my data from sqlite to postgres. All fields seem to work fine except for the duration field. It is shown as an integer in the admin panel and it does not let me edit or save.

It works fine with sqlite. However after moving to Postgres, I get the following errors (upon saving or updating):

ProgrammingError at /admin/content/movie/add/

column "duration" is of type bigint but expression is of type interval

HINT: You will need to rewrite or cast the expression.

class Movie(models.Model):
    """
    Stores movie related information.
    """
    unique_id = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    name = models.CharField(verbose_name='name', max_length=100, blank=False, unique=True)
    slug = models.SlugField(verbose_name='slug', null=True, blank=True)
    description = models.TextField(verbose_name='description', blank=True)
    poster = models.ImageField(verbose_name='poster',upload_to='posters/movies', default='defaults/video.webp', validators=[validate_image])
    # Field of interest
    duration = models.DurationField(verbose_name='duration')
    created_at = models.DateTimeField(verbose_name='created_at', auto_now_add=True)

I also used the following length method for successfully converting the field to human readable form (when using sqlite), but it does not work after moving to postgres. It says:

duration (int) does not have 'days' attribute

def length(self):
        """
        Converts timedelta into hours and minutes.
        """
        days, seconds = self.duration.days, self.duration.seconds
        hours = days * 24   seconds // 3600
        minutes = (seconds % 3600) // 60
        seconds = (seconds % 60)

        if hours == 0:
            length = f'{minutes} min'
        else:
            if minutes == 0:
                length = f'{hours} h'
            else:
                length = f'{hours} h {minutes} min'
        return length

Note: I used pgloader to load the sqlite data. https://pgloader.readthedocs.io/en/latest/quickstart.html#migrating-from-sqlite

CodePudding user response:

From here DurationField:

A field for storing periods of time - modeled in Python by timedelta. When used on PostgreSQL, the data type used is an interval and on Oracle the data type is INTERVAL DAY(9) TO SECOND(6). Otherwise a bigint of microseconds is used.

So you either need to make the field in the model an IntegerField to match the the data or change the data to a Postgres interval. Since in SQLite the data is stored as microseconds then something like:


select (1000000::text || ' microseconds')::interval;
 interval 
----------
 00:00:01

  • Related