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 dispel 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.
The record we will analyze has been last updated from a computer located in Indian/Reunion (UTC + 4) at 20:14:11.
So in UTC the write date should be 16:14:11
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 tod.isoformat(). - For a
datetimeinstance d,str(d)is equivalent tod.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' <!--kg-card-begin: html--> In these experiments, consider the current _uid is either user with id 2 or with id 3. And that these users initiated the action leading to these python pieces of code execution. <!--kg-card-end: html-->
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
Odoo stores the Datetime in the database as UTC timestamps.
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
_contextof the record and search for thetzkey containing the name of the timezone. - Or fall back to the
record.env.user.tzvalue.
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','>=', (context_today() - datetime.timedelta(days=7)).strftime('%%Y-%%m-%%d'))]"/>
<filter string="Last Month"
name="month"
domain="[('date','>=', (context_today() - datetime.timedelta(days=30)).strftime('%%Y-%%m-%%d'))]"/>
<filter string="Last Year"
name="year"
domain="[('date','>=', (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','>', (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', '<', (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
relativedeltathat 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
combinethis Date with atimerepresenting midnight0,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.
There is still a lot of examples I probably didn't think about, so please share them in the comment section and I will add them. The goal is to have an extensive set of common use cases for future developers to be comfortable working with Dates and Datetimes.
Thanks for reading, if you liked this article please consider:
- ☕️ Buying me a Coffee
- 🥳 Register on Codingdodo.com
Top comments (0)