4 ms·
No, for example, the datediff in years for New Year's Eve and New Year's Day should be 1 (because it spans a year boundary), but EXTRACT on the difference would
by cldellow 5y ago
No, for example, the datediff in years for New Year's Eve and New Year's Day should be 1 (because it spans a year boundary), but EXTRACT on the difference would give you 0:
select extract(year from '2021-01-01'::timestamptz - '2020-12-31'::timestamptz);
- cedricd 5y agoYep. That's exactly why this function isn't trivial to write. Boundaries are not obvious and native pg functions (insofar as I'm aware of them) don't do these kinds of diffs. Semantically the code has to diff days (for example) but be aware that you crossed a year boundary (or any other).
- mrkurt 5y agoAm I missing something here, or is the subtraction operator exactly right? > select ('2021-01-01'::timestamptz - '2020-12-31'::timestamptz) as diff; 1 day
- nicoburns 5y agoI think they want a function that output "1 year" given those two dates + "years" as arguments.
- erichocean 5y agoCorrect.
- castorp 5y agoBut 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
- wongarsu 5y agoYes, 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
- mrkurt 5y agoOh 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-functions.html#function_datediff 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
- jeroenhd 5y agoYears 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.
- funcDropShadow 5y agoSo, it is this: > # SELECT date_part('year', '2021-01-01'::timestamptz) - date_part('year', '2020-12-31'::timestamptz) as years; Or what am I missing? [edit: formatting]
- wavesquid 5y agoSo isn't that `age ( timestamp, timestamp ) → interval`?
- castorp 5y ago
- brandmeyer 5y agoWhy would you want this to round up instead of down or to nearest?
- wongarsu 5y agoIt's not even rounded up, datediff('years', 2020-01-01, 2021-12-31) returns 1. I'm not sure what it's used for though
- pella 5y agoalternative - for 1y results: select date_part('year','2021-01-01'::date) - date_part('year','2020-12-31'::date) as yeardiff; yeardiff ---------- 1 (1 row)
- d0100 5y agoextract(year from '2021-01-01'::timestamptz) - extract(year from '2020-12-31'::timestamptz) For months and days it would be a bit bigger
- tomrod 5y agoThat doesn't make sense to me. Why wouldn't datediff be 0 in this case, as it is less than 365 days? Else one should simply: extract(year from d1) - extract(year from d2) and call it a day?
- btilly 5y agoTry it again, except looking for months. You'll get -11 instead of 1.
- castorp 5y agoselect extract(month from justify_interval(timestamp '2022-01-01' - timestamp '2021-11-01')) returns 2
- saltcured 5y agoI'm pretty sure this will provide the desired semantics, though I am away from a usable postgres and not bothering to test it. CREATE FUNCTION date_diff(granularity text, t1 timestamptz, t2 timestamptz) RETURNS int8 AS $$ SELECT date_part(granularity, date_trunc(granularity, t2) - date_trunc(granularity, t1))::int8 $$ LANGUAGE SQL;
- castorp 5y ago> No, for example, the datediff in years for New Year's Eve and New Year's Day should be 1 I don't understand the purpose of such a calculation. If you want to check if two dates (or timestamps) fall into the same year, you compare the year part of them. If you want to check how far they are apart, you compare the difference between the two to an interval.