4 ms·

These are used pretty often when doing data analysis. It's a simple way to group things together. For example, find all users who have have been active at leas

These are used pretty often when doing data analysis. It's a simple way to group things together. For example, find all users who have have been active at least 10 days. This provides a simple way to do a complex operation (if a user signs up Dec 29, a naive implementation won't catch that their 10 days is in January of the next year).

jimktrains2 5y ago
In that example, what does knowing you've crossed 1 year boundry give you (i.e. gow do you use the numeric result?) And if it does matter why not group by the year?
- cedricd 5y ago
  The point of the function is that it doesn't matter you've crossed the year boundary when counting days. A naive implementation would accidentally get tripped up by the year -- extracting the 'day' part of a timestamp, as an integer, gives you the day from the start of the year. So on one side of the year boundary you have 365 and on the other you have 1. The way to do it correctly is to multiply the day in the year by the year itself so that a '1' on a later year is a bigger number. And of course grouping by year isn't always what you want to do :)
  - wongarsu 5y ago
    My naive implementation would have been to calculate `now() - signup_date > "10 days"::interval`. That doesn't trip over any year bounds, but if you sign up at 4pm then I wouldn't consider you until 4pm on the 10th day. Which makes total sense, but generally isn't how this is done when a human does it by hand.
    - to11mtm 5y ago
      I feel like cedricd and OP of this chain are talking about different things, or at least it's hard to discern from the conversation. If you care about Days, you normally absolutely -should- use DATEDIFF('DAY' instead of DATEDIFF('YEAR' and act accordingly. The bigger thing is the semantics they provide. Frankly, this can be a pain point at times, but IIRC PostgreSQL at least is very work-aroundable, IIRC you can get the equivalent for most common cases off the interval from an add/sub. Honestly, Dates in SQLite are harder to deal with in the long term, since without a native data type you have to write at least the level of conversions you would in PostgreSQL. (e.x. just convert everything to/from tics at the abstraction layer.) Edit: Also I would suggest considering instead use of getdate() <= DATEADD('day', signup_date,1) or a variant as that is probably more cross DB friendly
- wruza 5y ago
  It means “spanned which legal-y periods”, I think. E.g. if a user is billed weekly, billing should ~group 2 weeks, even if login events happened at Sat and Mon. As a former accounting consultant/developer I can guess use cases like “group fixed assets by how many tax amortization quarters they already took part in, and then …”.
  - to11mtm 5y ago
    Lol my favorite case of this was where an org could by regulation be paid no less than 30 days apart, but could not take more than 1 payment per month, and could not take a payment on Sundays or banking holidays. Solve for X.
    - naniwaduni 5y ago
      Find the minimum and maximum maximum number of consecutive payments this org can take?
mr_toad 5y ago
> These are used pretty often when doing data analysis. In my experience you almost always want to count intervals for analysis and not interval boundaries. For example when calculating someone’s age in years. The use cases for interval boundaries seem to mostly be businesses rules based on contract dates.
remram 5y ago
Normal subtraction works there so that's not a compelling example at all.
castorp 5y ago
> For example, find all users who have have been active at least 10 days. Well, then you only need to compare the difference of the timestamp (or date) values with an interval of 10 days. e.g. end_time - start_time >= interval '10 days'

jimktrains2 5y ago

In that example, what does knowing you've crossed 1 year boundry give you (i.e. gow do you use the numeric result?) And if it does matter why not group by the year?

cedricd 5y ago
The point of the function is that it doesn't matter you've crossed the year boundary when counting days. A naive implementation would accidentally get tripped up by the year -- extracting the 'day' part of a timestamp, as an integer, gives you the day from the start of the year. So on one side of the year boundary you have 365 and on the other you have 1. The way to do it correctly is to multiply the day in the year by the year itself so that a '1' on a later year is a bigger number. And of course grouping by year isn't always what you want to do :)
- wongarsu 5y ago
  My naive implementation would have been to calculate `now() - signup_date > "10 days"::interval`. That doesn't trip over any year bounds, but if you sign up at 4pm then I wouldn't consider you until 4pm on the 10th day. Which makes total sense, but generally isn't how this is done when a human does it by hand.
  - to11mtm 5y ago
    I feel like cedricd and OP of this chain are talking about different things, or at least it's hard to discern from the conversation. If you care about Days, you normally absolutely -should- use DATEDIFF('DAY' instead of DATEDIFF('YEAR' and act accordingly. The bigger thing is the semantics they provide. Frankly, this can be a pain point at times, but IIRC PostgreSQL at least is very work-aroundable, IIRC you can get the equivalent for most common cases off the interval from an add/sub. Honestly, Dates in SQLite are harder to deal with in the long term, since without a native data type you have to write at least the level of conversions you would in PostgreSQL. (e.x. just convert everything to/from tics at the abstraction layer.) Edit: Also I would suggest considering instead use of getdate() <= DATEADD('day', signup_date,1) or a variant as that is probably more cross DB friendly
wruza 5y ago
It means “spanned which legal-y periods”, I think. E.g. if a user is billed weekly, billing should ~group 2 weeks, even if login events happened at Sat and Mon. As a former accounting consultant/developer I can guess use cases like “group fixed assets by how many tax amortization quarters they already took part in, and then …”.
- to11mtm 5y ago
  Lol my favorite case of this was where an org could by regulation be paid no less than 30 days apart, but could not take more than 1 payment per month, and could not take a payment on Sundays or banking holidays. Solve for X.
  - naniwaduni 5y ago
    Find the minimum and maximum maximum number of consecutive payments this org can take?

mr_toad 5y ago

> These are used pretty often when doing data analysis. In my experience you almost always want to count intervals for analysis and not interval boundaries. For example when calculating someone’s age in years. The use cases for interval boundaries seem to mostly be businesses rules based on contract dates.

remram 5y ago

Normal subtraction works there so that's not a compelling example at all.

castorp 5y ago

> For example, find all users who have have been active at least 10 days. Well, then you only need to compare the difference of the timestamp (or date) values with an interval of 10 days. e.g. end_time - start_time >= interval '10 days'