Question: how to use .between() for date interval?

[circulon]

This is a general "how to" question

I was looking to use .between() for a date interval eg low: today - 7 days, high: date today.
Currently I'm using postgres so the where_raw looks like this

    .where_raw("\"{}\".\"updated_at\" BETWEEN current_date - integer '7' AND current_date".format(table_a))

which is very grammar specific.
is there a more db agnostic way to do this?

Happy to discuss
Thanks

[josephmancuso]

Can't you just use the between and pass 2 dates? Masonite ORM installs with pendulum so it would look something like:

Invoice.between('created_at', pendulum.now().subtract(days=7).to_datetime_string(), pendulum.now().to_datetime_string())

[Marlysson]

@josephmancuso There some method to simulate dates greater or lower than some point?

[josephmancuso]

@Marlysson what do you mean? for pendulum?

[josephmancuso]

https://pendulum.eustace.io/docs/#difference

[Marlysson]

No, in database level. Like this https://docs.djangoproject.com/en/3.1/ref/models/querysets/#year

[josephmancuso]

@Marlysson

User.where('created_at', '<=', pendulum.now().subtract(days=7).to_datetime_string())

[circulon]

Let me clarify

Consider this scenario:
A server is running a db engine in Sydney
The timezone for the server was never changed so its AEDT.
The database timezone is set for UTC to force a standard.
All Date columns are DateTime's ie include timezone data.

Another server runs the webserver is the US for example.

Won't generating a pendulum date on the webserver be transformed into an incorrect date when sent to the database?
Hence I am currently doing this on the db side prevents possible incorrect dates being used.

I'm concerned as I'm starting work with AWS services and the RDS could be in any timezone and the lambdas could be in any other timezone.

Am I overthinking this?
Date standardization and transforms are a PITA at the best of times as many devs know ;(

[josephmancuso]

I think so? date standardization is even more a PITA when you're trying to understand someone else PITA problem 😆

If all dates are in UTC then there should be no issues right?

If the db engine in Sydney has dates stored as UTC then it doesn't matter which dates you check against as long as all dates are in UTC you are checking against.

Its been a long day and I'm tired so my brain is shot as well .. In other words, if you make sure all dates are in the same timezone (UTC) across the board then I think you should be good

[circulon]

@josephmancuso

Thanks for the reply.
I will have to do some testing but yeah generating the pendulum dates in utc could work nicely.

Get some rest and keep up the great work
Thanks again

[josephmancuso]

👍 ❤️