Django Database Functions - Date functions

Django ORM은 데이터베이스 기능을 지원하기 위해 functions를 제공하고 있습니다. 이번 글에서는 날짜 functions들에 대해 알아보도록 하겠습니다. 

Extract

Extract는 날짜를 숫자로 변경합니다.

DateField, DateTimeField, TimeField 또는 DurationField의 값에 lookup_name을 사용하여 날짜의 일부를 숫자로 추출할 수 있습니다. lookup_name으로는 아래 목록들이 사용 가능합니다.

datetime 2015-06-15 23:30:01.000321+00:00

“year”: 2015
“iso_year”: 2015
“quarter”: 2
“month”: 6
“day”: 15
“week”: 25
“week_day”: 2
“iso_week_day”: 1
“hour”: 23
“minute”: 30
“second”: 1

>>> experiment = Sample.objects.annotate(
        start_year=Extract('start_datetime', 'year')).get()
>>> experiment.annotate_year
2015

만약 Django에서 시간대가 다르게 세팅되어 있다면 추출시에 시간대가 원하는 값과는 다를 수 있으니 확인이 필요합니다.

 

Extract와 논리적으로 동일하지만 날짜중에 특정 값만 추출 가능한 클래스도 존재합니다.

class ExtractYear(Extract):
    lookup_name = "year"


class ExtractIsoYear(Extract):
    """Return the ISO-8601 week-numbering year."""

    lookup_name = "iso_year"


class ExtractMonth(Extract):
    lookup_name = "month"


class ExtractDay(Extract):
    lookup_name = "day"


class ExtractWeek(Extract):
    """
    Return 1-52 or 53, based on ISO-8601, i.e., Monday is the first of the
    week.
    """

    lookup_name = "week"


class ExtractWeekDay(Extract):
    """
    Return Sunday=1 through Saturday=7.

    To replicate this in Python: (mydatetime.isoweekday() % 7) + 1
    """

    lookup_name = "week_day"


class ExtractIsoWeekDay(Extract):
    """Return Monday=1 through Sunday=7, based on ISO-8601."""

    lookup_name = "iso_week_day"


class ExtractQuarter(Extract):
    lookup_name = "quarter"


class ExtractHour(Extract):
    lookup_name = "hour"


class ExtractMinute(Extract):
    lookup_name = "minute"


class ExtractSecond(Extract):
    lookup_name = "second"

Now

Now는 쿼리가 실행될때 SQL을 사용하여 데이터베이스 서버의 현재 날짜와 시간을 반환합니다.

>>> from django.db.models.functions import Now
>>> Article.objects.filter(published__lte=Now())
<QuerySet [<Article: How to Django>]>

Trunc

Trunc는 원하는 요소까지 날짜를 자릅니다. 연도부터 초단위까지 있는 날짜를 원하는 요소까지 잘라서 사용하게 됩니다. 만약 자르게 된다면 잘린 값은 최소값으로 설정되게 됩니다.

datetime 2015-06-15 14:30:50.000321+00:00

“year”: 2015-01-01 00:00:00+00:00
“quarter”: 2015-04-01 00:00:00+00:00
“month”: 2015-06-01 00:00:00+00:00
“week”: 2015-06-15 00:00:00+00:00
“day”: 2015-06-15 00:00:00+00:00
“hour”: 2015-06-15 14:00:00+00:00
“minute”: 2015-06-15 14:30:00+00:00
“second”: 2015-06-15 14:30:50+00:00


Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321))
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123))
>>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999))
>>> experiments_per_day = Experiment.objects.annotate(
...    start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).values('start_day').annotate(experiments=Count('id'))
>>> for exp in experiments_per_day:
...     print(exp['start_day'], exp['experiments'])
...
2015-06-15 00:00:00 2
2015-12-25 00:00:00 1
>>> experiments = Experiment.objects.annotate(
...    start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).filter(start_day=datetime(2015, 6, 15))
>>> for exp in experiments:
...     print(exp.start_datetime)
...
2015-06-15 14:30:50.000321
2015-06-15 14:40:02.000123

Extract와 논리적으로 동일하지만 날짜중에 특정 값만 추출 가능한 클래스도 존재합니다.

class TruncYear(TruncBase):
    kind = "year"


class TruncQuarter(TruncBase):
    kind = "quarter"


class TruncMonth(TruncBase):
    kind = "month"


class TruncWeek(TruncBase):
    """Truncate to midnight on the Monday of the week."""

    kind = "week"


class TruncDay(TruncBase):
    kind = "day"


class TruncDate(TruncBase):
    kind = "date"
    lookup_name = "date"
    output_field = DateField()

    def as_sql(self, compiler, connection):
        # Cast to date rather than truncate to date.
        lhs, lhs_params = compiler.compile(self.lhs)
        tzname = self.get_tzname()
        sql = connection.ops.datetime_cast_date_sql(lhs, tzname)
        return sql, lhs_params


class TruncTime(TruncBase):
    kind = "time"
    lookup_name = "time"
    output_field = TimeField()

    def as_sql(self, compiler, connection):
        # Cast to time rather than truncate to time.
        lhs, lhs_params = compiler.compile(self.lhs)
        tzname = self.get_tzname()
        sql = connection.ops.datetime_cast_time_sql(lhs, tzname)
        return sql, lhs_params


class TruncHour(TruncBase):
    kind = "hour"


class TruncMinute(TruncBase):
    kind = "minute"


class TruncSecond(TruncBase):
    kind = "second"

class ExtractYear(Extract):
    lookup_name = "year"

class ExtractIsoYear(Extract):
    """Return the ISO-8601 week-numbering year."""
    lookup_name = "iso_year"

class ExtractMonth(Extract):
    lookup_name = "month"

class ExtractDay(Extract):
    lookup_name = "day"

class ExtractWeek(Extract):
    """
    Return 1-52 or 53, based on ISO-8601, i.e., Monday is the first of the
    week.
    """
    lookup_name = "week"

class ExtractWeekDay(Extract):
    """
    Return Sunday=1 through Saturday=7.
    To replicate this in Python: (mydatetime.isoweekday() % 7) + 1
    """
    lookup_name = "week_day"

class ExtractIsoWeekDay(Extract):
    """Return Monday=1 through Sunday=7, based on ISO-8601."""
    lookup_name = "iso_week_day"

class ExtractQuarter(Extract):
    lookup_name = "quarter"

class ExtractHour(Extract):
    lookup_name = "hour"

class ExtractMinute(Extract):
    lookup_name = "minute"

class ExtractSecond(Extract):
    lookup_name = "second"

만약 Django에서 시간대가 다르게 세팅되어 있다면 추출시에 시간대가 원하는 값과는 다를 수 있으니 확인이 필요합니다.