Working with Dates in Odoo - Analysis and Use Cases

Working with Dates and Datetime can be very complicated, especially when you take into account TimeZone. In this how-to guide, we will go through Odoo's source code to analyze and make sense of it all. Then we will review common use cases around this topic in reports, website, backend, ORM, and hopefully dispell some of the mystery.

This analysis will cover Odoo versions 12, 13, 14, and onwards.

Analysis

In this section, we will analyze how Odoo works from experimentation and Code Analysis.

Consider the following basic model

# -*- coding: utf-8 -*-
from odoo import models, fields

class BlogPost(models.Model):
    _name = 'blog.post'
    _description = 'Blog Post'

    name = fields.Char('Title', required=True)
    date_release = fields.Date('Release Date')
    # write_date hidden field present on all models as a fields.Datetime

date_release is a Date field and write_date (filled by Odoo when a user updates the model) is a Datetime field.

With that knowledge, let's dive in.

From Odoo 12.0 onwards, Dates and Datetimes are python objects.

One of the big changes that came in Odoo 12.0 was that Odoo Field returns always native Python objects, so no more fields.Date/Datetime.from_string is needed.

For converting old-style Date/Datetimes strings to Python objects, use fields.Date.to_date , and fields.Datetime.to_datetime methods.

How does it work In the Backend?

Odoo ORM

Let's see what happens when we do some basic operations with the ORM.

blogpost = self.env['blog.post'].search([], limit=1)

type(blogpost.date_release)
# <class 'datetime.date'>
blogpost.date_release
# datetime.date(2021, 4, 14)
str(blogpost.date_release)
# 2021-04-14

type(blogpost.write_date)
# <class 'datetime.datetime'>
blogpost.write_date
# datetime.datetime(2021, 4, 7, 16, 14, 11, 776876)
str(blogpost.write_date)
# 2021-04-07 16:14:11.776876

Datetime and Date objects are well documented here but these are some of the things we need to know:

String representation

• For a date d, str(d) is equivalent to d.isoformat().
• For a datetime instance d, str(d) is equivalent to d.isoformat(' ').

This string representation happens automatically when you try for example to log the Date into a string or do a string format function.

ORM Datetime objects are naive.

Datetime objects can be "aware" or "naive" and the one returned by Odoo ORM is naive, it can be verified by the fact that the property tzinfo of the object is None.

print(blogpost.write_date.tzinfo)
# None

Is it aware of the user timezone or timezone context?

Inside odoo _context dictionary there is a key called tz that can be filled with the timezone name, eg 'Europe/Brussels' or 'Indian/Reunion'.

Consider these two users

• res.users(2,) has timezone set to 'Indian/Reunion'
• res.users(6,) has timezone set to 'Europe/Berlin'

blogpost_user2 = self.env['blog.post'].with_user(2).browse(1)

blogpost_user2.env.user.tz
# >> 'Indian/Reunion'

blogpost_user2._context
# >> {'lang': 'en_US', 'tz': 'Indian/Reunion', 'uid': 2, 'allowed_company_ids': [1]}

str(blogpost_user2.write_date)
# >> 2021-04-07 16:14:11.776876

blogpost_user6 = self.env['blog.post'].with_user(6).browse(1)

blogpost_user6.env.user.tz
# >> 'Europe/Berlin'

blogpost_user6._context
# >> {'lang': 'en_US', 'tz': 'Europe/Berlin', 'uid': 6, 'allowed_company_ids': [1]}

str(blogpost_user2.write_date)
# >> 2021-04-07 16:14:11.776876

We can see that during the python execution via the ORM, the dates are always still UTC and not converted to the timezone of the user browsing even with context.

Dates and Datetime in PostgreSQL + psycopg

PostgreSQL

If we take a look into the database table description

    Column    |            Type             | Collation | Nullable |                Default
--------------+-----------------------------+-----------+----------+---------------------------------------
 id           | integer                     |           | not null | nextval('blog_post_id_seq'::regclass)
 name         | character varying           |           | not null |
 date_release | date                        |           |          |
 state        | character varying           |           |          |
 content      | text                        |           |          |
 write_date   | timestamp without time zone |           |          |

We can see that fields.Date create a date type and fields.Datetime create a timestamp without time zone column type in PostgreSQL.

 id | date_release |         write_date
----+--------------+----------------------------
  1 | 2021-04-14   | 2021-04-07 16:14:11.776876

psycopg

Now if we use the env cursor in Odoo to do our own requests

self.env.cr.execute("SELECT write_date, date_release FROM blog_post where id = 1")
self.env.cr.fetchall()
# results: [(datetime.datetime(2021, 4, 7, 16, 14, 11, 776876), datetime.date(2021, 4, 14))]

We can see that psycopg transforms the Dates and Datetimes into python's respective objects.

Again these Datetimes are in UTC.

How does Odoo convert to Timezone?

In WebClient views

We saw that Odoo ORM doesn't do any conversion when fetching data even with a context or with a specific user, without extra work. Still, when we view the records from the client-side they appear in our Timezone.

Computer with a clock set to 'Indian/Reunion' (UTC +4), from user 2 with tz 'Indian/Reunion' (UTC +4):

Computer with a clock set to 'Indian/Reunion' (UTC +4), from user 6 with tz 'Europe/Berlin' (UTC + 2 // summer):

We can see that a timezone conversion happened into UTC +4, but it took into account only our Browser client clock and not the timezone set as a setting for the user.

Network response from the client

{
	author_ids: [26]
	content: "..."
	date_release: "2021-04-14"
	display_name: "Installation"
	id: 1
	name: "Installation"
	state: "published"
	write_date: "2021-04-07 16:14:11"
}

Then inside odoo/addons/web/static/src/js/views/abstract_model.js the function _parseServerValue handle the conversion of the field from string to Moment object

/**
 * Processes date(time) and selection field values sent by the server.
 * Converts data(time) values to moment instances.
 * Converts false values of selection fields to 0 if 0 is a valid key,
 * because the server doesn't make a distinction between false and 0, and
 * always sends false when value is 0.
 *
 * @param {Object} field the field description
 * @param {*} value
 * @returns {*} the processed value
 */
_parseServerValue: function (field, value) {
    if (field.type === 'date' || field.type === 'datetime') {
        // process date(time): convert into a moment instance
        value = fieldUtils.parse[field.type](value, field, {isUTC: true});
    } else if (field.type === 'selection' && value === false) {
        // process selection: convert false to 0, if 0 is a valid key
        var hasKey0 = _.find(field.selection, function (option) {
            return option[0] === 0;
        });
        value = hasKey0 ? 0 : value;
    }
    return value;
},

This function calls the parse function of the 'web.field_utils' module defined in  odoo/addons/web/static/src/js/fields/field_utils.js the will then call the parseDateTime function when it sees that the field is of type datetime.

function parseDateTime(value, field, options) {
    if (!value) {
        return false;
    }
    var datePattern = time.getLangDateFormat(),
        timePattern = time.getLangTimeFormat();
    var datePatternWoZero = datePattern.replace('MM','M').replace('DD','D'),
        timePatternWoZero = timePattern.replace('HH','H').replace('mm','m').replace('ss','s');
    var pattern1 = datePattern + ' ' + timePattern;
    var pattern2 = datePatternWoZero + ' ' + timePatternWoZero;
    var datetime;
    const smartDate = parseSmartDateInput(value);
    if (smartDate) {
        datetime = smartDate;
    } else {
        if (options && options.isUTC) {
            value = value.padStart(19, "0"); // server may send "932-10-10" for "0932-10-10" on some OS
            // phatomjs crash if we don't use this format
            datetime = moment.utc(value.replace(' ', 'T') + 'Z');
        } else {
            datetime = moment.utc(value, [pattern1, pattern2, moment.ISO_8601]);
            if (options && options.timezone) {
                datetime.add(-session.getTZOffset(datetime), 'minutes');
            }
        }
    }
    // ...

the getTZOffset just calls the basic Date JavaScript object function getTimezoneOffset so there is no user Timezone / Context timezone taken into consideration. It is purely based on the Client Browser Timezone.

Inverse operations are done when sending RPC calls to the server, the UTC value of the moment object is sent as data.

In reports

Consider this simple Report document

<template id="report_blog_post">
    <t t-call="web.html_container">
        <t t-foreach="docs" t-as="o">
            <t t-call="web.external_layout">
                <div class="page">
                    <h2 t-field="o.name"></h2>
                    <p>Write date <span t-field="o.write_date"/></p>
                    <div>
                        <div t-field="o.content"></div>div>
                    </div>
                </div>
            </t>
        </t>
    </t>
</template>

Same computer setup set in Timezone UTC +4 'Indian/Reunion'.

For our User 2 with Timezone "Indian/Reunion" (UTC +4), the report gives us

From the same computer, User 6 with Timezone "Europe/Berlin" (UTC + 2), the report gives us

How does it work?

In the file odoo/odoo/addons/base/models/ir_qweb_fields.py

class DateTimeConverter(models.AbstractModel):
    _name = 'ir.qweb.field.datetime'
    _description = 'Qweb Field Datetime'
    _inherit = 'ir.qweb.field'

	@api.model
    def value_to_html(self, value, options):
        if not value:
            return ''
        options = options or {}
        // value is 2021-04-07 16:14:11.776876

        lang = self.user_lang()
        locale = babel_locale_parse(lang.code)
        format_func = babel.dates.format_datetime
        if isinstance(value, str):
            value = fields.Datetime.from_string(value)

        value = fields.Datetime.context_timestamp(self, value)
        // value is 2021-04-07 20:14:11.776876+04:00
        //...

context_timestamp is a static function on the field Datetime Class and is used to convert to the timezone passed in context or fallback to the timezone of the user via record.env.user.tz.

In Website/Portal views

For website and portal views, it works the same way as in a report, the browser client timezone is not used.

<div class="col-xs-12">
    <t t-foreach="blog_posts" t-as="blog_post">
        <h3 t-field="blog_post.name"></h3>
        Last updated <span t-field="blog_post.write_date"/>
        <br/>
        Test context-timestamp: <span t-esc="blog_post.write_date" t-options="{'widget': 'datetime'}"/>
        <br/>
        Test Europe/Berlin: <span t-esc="blog_post.write_date" t-options="{'widget': 'datetime', 'tz_name': 'Europe/Berlin'}"/>
        <hr/>
    </t>
</div>

With user 2 (Preference TZ is set to 'Indian/Reunion')

Using t-field on a real Datetime field or using the options widget 'datetime' helps us achieve the converted timezone display.

How does it work?

It works the same way as in reports, using the DateTimeConverter function  value_to_html.

So, be careful when you get user input from the website, you should convert it to UTC before sending it to the server if you use Moment.js and handle the ajax request yourself.

During export

When you use the Odoo export functionality, the browser Timezone is not taken into consideration.

For our User 2 with Timezone "Indian/Reunion" (UTC +4), the CSV export gives us

"Title","Release Date","Last Updated on"
"Installation","2021-04-14","2021-04-07 20:14:11"

From the same computer, User 6 with Timezone "Europe/Berlin" (UTC +2), the CSV export contains

"Title","Release Date","Last Updated on"
"Installation","2021-04-14","2021-04-07 18:14:11"

How does it work?

The logic is contained inside odoo/odoo/fields.py inside the Datetime Field.

def convert_to_export(self, value, record):
    if not value:
        return ''
    value = self.convert_to_display_name(value, record)
    return self.from_string(value)

def convert_to_display_name(self, value, record):
    assert record, 'Record expected'
    return Datetime.to_string(Datetime.context_timestamp(record, Datetime.from_string(value)))

The convert_to_export method is used on every field to add some presentation layer when the fields are to be exported. Here we can see that the context_timestamp is used to calculate the Datetime into the user Timezone.

This timezone is fetched from the record._context.get('tz) , or record.env.user.tz. So the record browsed must have the correct info either by having the correct User that did the action or with_context() specifying the Timezone.

Dates and Datetimes common use cases

Now let's go over some common uses

Assign the current Datetime to a field in ORM

You should assign written value in UTC so use this utility function

my_model.write({
	'datetime_field': fields.Datetime.now()
})

Using Python basic datetime.now() is not good enough because you will have to remove microseconds to comply with the server Datetime default format

Assign the current Date to a field in ORM

my_model.write({
	'date_field': fields.Date.context_today(my_model)
})

context_today expect a record as the argument so either give it self, containing the record you are on, or an explicit record. The function itself will:

• Look inside the _context of the record and search for the tz key containing the name of the timezone.
• Or fall back to the record.env.user.tz value.

Set a Datetime as Now + 600 seconds

Let's say you need to set a short expiration Datetime for some token access or any short-time access record. You will want to have an expiration Datetime 10 min in the future:

my_model.write({
	'token_expiration_datetime': fields.Datetime.now() + timedelta(minutes=10) 
})

now() function on Datetime class is in UTC.

Example uses in search method of the ORM

Get records younger than 3 days old

Date Field

from odoo import fields
from dateutil.relativedelta import relativedelta

date_limit = fields.Date.context_today(self) - relativedelta(days=3)
results = self.env['my.model'].search([
	('my_date_field', '>' date_limit)
])

Datetime Field

from odoo import fields
from dateutil.relativedelta import relativedelta

datetime_limit = fields.Datetime.now() - relativedelta(days=3)
results = self.env['my.model'].search([
	('my_datetime_field', '>' datetime_limit)
])

See the relativedelta documentation for more information and be careful that -

• singular arguments like year, hour, etc, REPLACE the corresponding value in the original Datetime
• plural arguments like years, hours, etc, DO THE ARITHMETIC operation on the original Datetime.

For simpler operations that don't need REPLACING value of a Datetime and just do simple arithmetic operations, you can use timedelta

from odoo import fields
from datetime import timedelta

datetime_limit = fields.Datetime.now() - timedelta(days=3)
results = self.env['my.model'].search([
	('my_datetime_field', '>' datetime_limit)
])

Example converting a Date to Datetime 7:00 AM

Let's say you have a Date and you want to transform it into a Datetime with hour 00:00:00

import datetime

# record.initial_date is for this example 2021-04-21
new_datetime = datetime.combine(record.initial_date, datetime.time(7, 0))

# We can also do
datetime.combine(record.initial_date, datetime.time.min).replace(hour=7)

Odoo Dates and Datetimes utils functions

Odoo gives us some useful functions to handle Dates and Datetimes manipulation that are located in odoo/odoo/tools/date_utils.py

start_of and end_of

Given a current Date or Datetime we can use these functions to get the boundaries of a time period surrounding it.

The granularity can be year, quarter, month, week, day, hour.

from odoo.tools import date_utils

today = fields.Date.today()
first_day = date_utils.start_of(today, 'week')
last_day = date_utils.end_of(today, 'week')

date_range(start, end, step=relativedelta(months=1)) to get an iterator of Datetimes between start and end

from odoo.tools import date_utils

start = datetime.datetime(2021, 1, 1)
end = datetime.datetime(2021, 4, 1)

result = date_utils.date_range(start, end)

# result = [
#     datetime.datetime(2021, 1, 1, 0, 0),
#     datetime.datetime(2021, 2, 1, 0, 0),
#     datetime.datetime(2021, 3, 1, 0, 0),
#     datetime.datetime(2021, 4, 1, 0, 0),
# ]

get_quarter for the start and end Dates of the quarter surrounding a Date

from odoo.tools import date_utils

today = fields.Date.today()
quarter_start, quarter_end = date_utils.get_quarter(today)

get_month for the start and end Dates of the month surrounding a Date

from odoo.tools import date_utils

today = fields.Date.today()
month_start, month_end = date_utils.get_month(today)

Printing current Datetime (for user Timezone) in QWeb report

Inside a report, you have access to the context_timestamp function, you can see this inside the _render_template of base ir_actions_report.

# def _render_template(self, template, values=None):
values.update(
    time=time,
    context_timestamp=lambda t: fields.Datetime.context_timestamp(self.with_context(tz=user.tz), t),
    user=user,
    res_company=user.company_id,
    website=website,
    web_base_url=self.env['ir.config_parameter'].sudo().get_param('web.base.url', default=''),
)
return view_obj._render_template(template, values)

Note that you also have access to time and datetime module, so to print the current datetime:

<span t-esc="context_timestamp(datetime.datetime.now()).strftime('%Y-%m-%d %H:%M')"/>

Qweb filter on Date Last Week, Month, Year

<filter string="Last Week" 
        name="week" 
        domain="[('date','&gt;=', (context_today() - datetime.timedelta(days=7)).strftime('%%Y-%%m-%%d'))]"/>
<filter string="Last Month" 
        name="month" 
        domain="[('date','&gt;=', (context_today() - datetime.timedelta(days=30)).strftime('%%Y-%%m-%%d'))]"/>
<filter string="Last Year" 
        name="year" 
        domain="[('date','&gt;=', (context_today() - datetime.timedelta(days=365)).strftime('%%Y-%%m-%%d'))]"/>

Qweb filter on Datetime last 24h hours.

<filter string="Last 24h"
        name="last_24h"  
        domain="[('start_date','&gt;', (context_today() - datetime.timedelta(days=1)).strftime('%%Y-%%m-%%d') )]"/>

QWeb filter on Datetime: this week.

The goal here is to filter between last Monday 00:00 and next Monday 00:00

<filter name="start_date_filter" 
        string="This Week" 
        domain="[
('start_date', '>=', (datetime.datetime.combine(context_today() + relativedelta(weeks=-1,days=1,weekday=0), datetime.time(0,0,0)).to_utc()).strftime('%Y-%m-%d %H:%M:%S')),
('start_date', '&lt;', (datetime.datetime.combine(context_today() + relativedelta(days=1,weekday=0), datetime.time(0,0,0)).to_utc()).strftime('%Y-%m-%d %H:%M:%S'))
        ]"
/>

Let's explain this part

datetime.datetime.combine(context_today() + relativedelta(days=1,weekday=0), datetime.time(0,0,0)).to_utc()

• We use context_today() to get the current Date
• Add relativedelta that will, (in this order) add 1 day, then replace with Monday. The Monday chosen will automatically be the next Monday relative to the context_today + 1 day. So if we are on a current Monday it will get us the next Monday and if we are on a Sunday it will be also okay.
• We combine this Date with a time representing midnight 0,0,0.
• Convert back to UTC.

For the "Last Monday" it is the same logic by using weeks=-1 and then finding next Monday.

Basic use case on the Website/Portal

Displaying the datepicker with browser timezone in your template

This is the basic structure to have the Datepicker widget trigger in your website template

<form action="/ssg_post_update" method="POST" class="pl-0 col-6 ssg-blog-form">
	<input type="hidden" name="blog_post_id" t-att-value="blog_post.id" />
	<t t-set="datepickerID" t-value="'datepicker' + str(blog_post.id)"/>
	<div class="s_website_form_datetime input-group date" 
     	 t-att-id="datepickerID" 
     	 data-target-input="nearest">
    	<t t-set='datetime_test'><t t-options='{"widget": "datetime"}' t-esc="blog_post.datetime_test"/></t>
    	<input
			type="text"
	        class="form-control datetimepicker-input s_website_form_input"
    	    t-attf-data-target="##{datepickerID}"
        	name="datetime_test"
	        placeholder="Chose date"
	        t-attf-id="datetime_test#{blog_post.id}"
	        t-att-value="datetime_test"
    	/>
	    <div class="input-group-append" t-attf-data-target="##{datepickerID}" data-toggle="datetimepicker">
    	    <div class="input-group-text">
        	    <i class="fa fa-calendar"></i>
	        </div>
    	</div>
	</div>
</form>

The problem here is that the value in the form is still a pure string in the Timezone of the user.

So if we submit it directly it will arrive on the server in an unadapted format and not in UTC. We could think that we have the user so we also have the user Timezone and with that, we could convert it to UTC before storing it. But Moment.js used by the datetimepicker base timezone on the Browser clock.

So it is better to handle it with JavaScript, by preventing the submit, converting data, and then send it.

var $form = $(ev.currentTarget);
ev.preventDefault();

// get the Moment.js Datetime object
let datetime_test = $form.find("input[name=datetime_test]").datetimepicker('viewDate').clone().locale('en');
let blog_post_id = parseInt($form.find("input[name=blog_post_id]").val());
// Convert to UTC
datetime_test.utc();
// Convert to SERVER FORMAT
datetime_test = datetime_test.format('YYYY-MM-DD HH:mm:ss')

this._rpc({
    route: "/ssg_post_update_json",
    params: {
        blog_post_id: blog_post_id,
        datetime_test: datetime_test
    },
}).then(function (data) {
    window.location.reload();
})

Now that we send data in UTC we can update our record directly in our controller

@http.route(['/ssg_post_update_json',], type='json', method="POST", auth="public", website=True, sitemap=True)
def ssg_post_update_json(self, blog_post_id, datetime_test, **kwargs):
    blog_post = request.env['blog.post'].browse(blog_post_id)
    if blog_post.exists():
        blog_post.write({
            'datetime_test': fields.Datetime.from_string(datetime_test)
        })
    return {}

Here we used fields.Datetime.from_string function to get a Datetime Python Object. But this is also correct:

# Insert directly as a string
blog_post.write({
	'datetime_test': datetime_test 
})

Be careful when you write directly as string, the Datetime must be in DATETIME_FORMAT which by default is %Y-%m-%d %H:%M:%S.

The string to Object conversion is done automatically in the to_datetime method inside the class Datetime(Field).

Moment.js

Moment.js has good documentation giving us examples of manipulations so I will advise you to read it to be more familiar.

The Moment.js object we are manipulating are oftentimes already converted into User Timezone so it is necessary to use utc() function when we send back data to be stored server-side.

Conclusion

To recap our analysis, internal handling of times in Odoo is done in UTC, and display in the web client is done with local time of the user's browser.

Be careful of the addons you install, if default Dates are set in UTC with time.strftime for example, causing problems when the time is near day breaks and offset, always use context_today.

If 2 users living in different places want to have identical reports they probably should switch their time to UTC. If you work with accountants they should be aware of that info and you should plan accordingly.

Thank you for reading ?, if you like that kind of analysis where we try to understand how things work inside the Odoo Framework please consider subscribing and following me on Twitter.

You may also be interested in these in-depth analyses:

• Deep-dive into the OWL Framework in this 4 parts article series where we create a real-world app from scratch.
• Understanding Odoo 14 mechanisms to extend, override, monkey-patch OWL Components with examples use-cases.