I have problems to make a query when comparing dates in different formats


Good afternoon, I would like to know if you could help me with this problem. I have this model where I record the sales made by each user on a default date and that is in datetime

now = datetime.datetime.now()
                Field('usuario', db.usuarios, label=T('Usuario')),
                Field('fecha_venta', 'datetime', label=T('Fecha de Venta'), default=now, writable=False),
                Field('codigo_venta', 'string', label=T('Codigo de Venta')),
                Field('detalle_venta', 'string', label=T('Detalle de Venta')),
                Field('tipo_venta', db.tipos_ventas, label=T('Tipo de Venta')),
                Field('destino', 'string', label=T('Destino de Venta')),

Now, with this model I want to make a query comparing data entered with a sqlform.factory like this

data = SQLFORM.factory(
    Field('usuario', db.usuarios, label=T('Usuario')),
    Field('fecha_inicial', 'date'),
    Field('fecha_final', 'date'),

if form.process().accepted:
        usuario = data .vars.usuario
        inicio= data .vars.fecha_inicial
        fin= data .vars.fecha_final
new_data = db((db.registros_ventas.usuario == usuario) &
                  ((db.registros_ventas.fecha_venta>= fecha_inicial) & (db.registros_ventas.fecha_venta<= fecha_final))
                db.usuarios.on(db.usuarios.id == db.registros_ventas.usuario),
return dict(new_data = new_data )

My problem is that I can not make a query comparing the dates in datetime() with those of type date() then I tried to change format but I have not obtained results, I would like to know what is the appropriate way to do it and the way to go, someone to support me :(?

asked by Alejandra Del Castillo 08.09.2016 в 21:30

1 answer


A date is not the same as a date and a time . To see if a datetime is within a range of date s you could convert the date s to the datetime initial of the day of inicio and the datetime final of the day of fin . Something like this:

import datetime as dt

fechahora = dt.datetime(2000, 10, 2, 22, 30, 43) # 2000-10-02 22:30:43
inicio = dt.date(2000, 10, 1) # 2000-10-01
fin = dt.date(2000, 10, 27) # 2000-10-27

inicio2 = dt.datetime(inicio.year, inicio.month, inicio.day, 0, 0, 0) # 2000-10-01 00:00:00
fin2 = dt.datetime(fin.year, fin.month, fin.day, 23, 59, 59) # 2000-10-27 23:59:59

print(inicio2 <= fechahora <= fin2)
answered by 09.09.2016 / 09:11