undefined | Better HN

0 pointsmrkurt4y ago0 comments

Am I missing something here, or is the subtraction operator exactly right?

    > select ('2021-01-01'::timestamptz - '2020-12-31'::timestamptz) as diff;
    1 day

0 comments

9 comments · 2 top-level

wongarsu4y ago· 5 in thread

Yes, but that's not what datediff does. The article doesn't really make it clear, but Microsoft explains Datediff as "This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate." That's very different from "the difference between two date" that is what the subtract operator calculates

mrkurtOP4y ago

Oh I see! What a weird function. I've used datediff in mysql and it's ... different: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functi...

So this is correct for years, right?

    > select floor(extract(epoch from date_trunc('year', '2021-01-01'::timestamptz) - date_trunc('year', '2020-12-31'::timestamptz)) / 3600 / 24 / 365) as diff;
    1

jeroenhd4y ago

Years aren't 365 days long, though. Technically, there are about 365.24 days in a year, but human dating doesn't take that into account well. The 3600 * 24 * 365 approach is tricky because there's a 1/4 chance that it'll work for you when you test it...

I don't think there's a good, mathematical expression for determining something as arbitrary as dates. If there was, I don't think MS would implement a special function like DateDiff for it.

1 more reply

castorp4y ago

The question is: what do you do with the result?

If you e.g. want to check if two timestamps are more than a year apart you just compare the difference to an interval of 1 year.

wavesquid4y ago

So isn't that `age ( timestamp, timestamp ) → interval`?

ryzvonusef4y ago

isn't that what the AGE function do?

nicoburns4y ago· 2 in thread

I think they want a function that output "1 year" given those two dates + "years" as arguments.

castorp4y ago

But the difference between 2021-01-01 and 2020-12-31 is only one day, not a year.

It gets even strange for e.g. select DATEDIFF(year, '2021-12-01', '2022-01-01') which still returns 1 even though that's a whole month. I don't see a use case for this kind of result

erichocean4y ago

Correct.

j / k navigate · click thread line to collapse

0 comments

9 comments · 2 top-level

wongarsu4y ago· 5 in thread

mrkurtOP4y ago

Oh I see! What a weird function. I've used datediff in mysql and it's ... different: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functi...

So this is correct for years, right?

    > select floor(extract(epoch from date_trunc('year', '2021-01-01'::timestamptz) - date_trunc('year', '2020-12-31'::timestamptz)) / 3600 / 24 / 365) as diff;
    1

jeroenhd4y ago

I don't think there's a good, mathematical expression for determining something as arbitrary as dates. If there was, I don't think MS would implement a special function like DateDiff for it.

1 more reply

castorp4y ago

The question is: what do you do with the result?

If you e.g. want to check if two timestamps are more than a year apart you just compare the difference to an interval of 1 year.

wavesquid4y ago

So isn't that `age ( timestamp, timestamp ) → interval`?

ryzvonusef4y ago

isn't that what the AGE function do?

nicoburns4y ago· 2 in thread

I think they want a function that output "1 year" given those two dates + "years" as arguments.

castorp4y ago

But the difference between 2021-01-01 and 2020-12-31 is only one day, not a year.

It gets even strange for e.g. select DATEDIFF(year, '2021-12-01', '2022-01-01') which still returns 1 even though that's a whole month. I don't see a use case for this kind of result

erichocean4y ago

Correct.

j / k navigate · click thread line to collapse