Converting a datetime to a date in database

[pokoli (Sergi Almacellas Abellana)]

Continuing the discussion from Search inside Dict:

@edbo

The only thing missing now is getting the date from a datetime stamp in the database. E.g. you have a datetime column called “storedate” with the value 2018-12-04 23:00:10. In PostgreSQL you can get only the date by calling storedate::date or only the time bij calling storedate::time. But I think this is something for the python-sql module.

The Cast function from python-sql can be used to convert from datetime (or timestampt) to date:

>>> from sql import Table, Cast
>>> t = Table('a')
>>> tuple(t.select(Cast(t.date, 'DATE')))
('SELECT CAST("a"."date" AS DATE) FROM "a" AS "a"', ())

In tryton you can use sql_type function to determine the database type used to represent a field.

So inside tryton you can use:

Model = Pool().get('model.name')
table = Model.__table__()
table.select(Cast(table.datetime, Model.date.sql_type().base)              

To select the value of the datetime field converted to the date field sql type.

Hope it helps

[fransuar (Francisco Maria Moyano Casco)]

Hi. I am trying to make this work, but I am constantly failing. A little help please
I have a datetime field, name first_contact on a model named contact_tracing. So according to the previous answer, to cast it like a date type, I would have to make something like this.

ContactTracing = Pool().get('contact_tracing')
table = ContactTracing.__table__()
query0 = table.select(Cast(table.first_contact.datetime, ContactTracing.date.sql_type().base))

Obviously I am doing something wrong.

[pokoli (Sergi Almacellas Abellana)]

@fransuar

query0 = table.select(Cast(table.first_contact.datetime, ContactTracing.date.sql_type().base))

You should use table.first_contact to select the first_contact column instead of table.first_contact.datetime and you should use the name of a date field instead of ‘date’ in the second part of the cast

[fransuar (Francisco Maria Moyano Casco)]

@pokoli

…and you should use the name of a date field instead of ‘date’ in the second part of the cast

It could be any field from any table, right? It just needs to be a date field

[pokoli (Sergi Almacellas Abellana)]

Yes, any date field is valid for the cast