Merge Date Time in Django ORM

Question

I was trying to combine date and time. For that i was using datetime.combine. But it is giving AttributeError module 'datetime' has no attribute 'combine'

TimesheetEntry.objects.filter(
    timesheet_users = user_id
    ).order_by(
        '-id'
    ).annotate(
        timesheet_clock_in=datetime.combine('timesheet_clock_in_date', 'timesheet_clock_in_time') 
    ).annotate(
        timesheet_clock_out=datetime.combine('timesheet_clock_out_date', 'timesheet_clock_out_time')
    ).values_list(
        'timesheet_clock_in',
        'timesheet_clock_out',
        'timesheet_jobs',
        'timesheet_note',
        'timesheet_clock_in_by'
    )

I know the error is in annotate but i don't how to solve it. Query works without annotate

Answer 1

In addition to this answer, use F() expressionas

from django.db.models import F
from datetime import datetime

TimesheetEntry.objects.filter(
    timesheet_users=user_id
).order_by(
    '-id'
).annotate(
    timesheet_clock_in=datetime.combine(F('timesheet_clock_in_date'), F('timesheet_clock_in_time'))
).annotate(
    timesheet_clock_out=datetime.combine(F('timesheet_clock_out_date'), F('timesheet_clock_out_time'))
).values_list(
    'timesheet_clock_in',
    'timesheet_clock_out',
    'timesheet_jobs',
    'timesheet_note',
    'timesheet_clock_in_by'
)

Answer 2

Did you import datetime correctly?

import datetime
# The datetime module

or

from datetime import datetime
# The datetime class in the datetime module

In the first case you should call datetime.datetime.combine (with 2x datetime). In the second case you can call datetime.combine directly.

Answer 3

date.combine() does not work because it raises argument 1 must be datetime.date, not F

Here is how we approached this issue:

from django.db.models import Value
from django.db.models.functions import Cast, Concat

TimesheetEntry.objects.filter(
    timesheet_users=user_id
).order_by(
    '-id'
).annotate(
    timesheet_clock_in=Cast(
        Concat('timesheet_clock_in_date', Value(" "), 'timesheet_clock_in_time', output_field=DateTimeField()),
        output_field=DateTimeField()
    )
    timesheet_clock_out=Cast(
        Concat('timesheet_clock_out_date', Value(" "), 'timesheet_clock_out_time', output_field=DateTimeField()),
        output_field=DateTimeField()
    )
).values_list(
    'timesheet_clock_in',
    'timesheet_clock_out',
    'timesheet_jobs',
    'timesheet_note',
    'timesheet_clock_in_by'
)

Answer 4

You can do it in the database instead of in the ORM:

TimesheetEntry.objects.filter(
    timesheet_users = user_id
    ).order_by(
        '-id'
    ).annotate(
        timesheet_clock_in=ExpressionWrapper(F("timesheet_clock_in_date") + F("timesheet_clock_in_time"), output_field=DateTimeField())
    ).annotate(
        timesheet_clock_out=ExpressionWrapper(F("timesheet_clock_out_date") + F("timesheet_clock_out_time"), output_field=DateTimeField())
    ).values_list(
        'timesheet_clock_in',
        'timesheet_clock_out',
        'timesheet_jobs',
        'timesheet_note',
        'timesheet_clock_in_by'
    )

+ is the Postgres operator for date + time ? timestamp so make sure you're using the correct operator for your database.

Also as a note to anyone doing this: Be careful of timezone issues when combining date and time into timestamps.