r/programming • • Nov 16 '14

PostgreSQL vs. MS SQL Server - A comparison of two relational databases from the point of view of a data analyst

http://www.pg-versus-ms.com/
171 Upvotes

321 comments sorted by

View all comments

Show parent comments

19

u/squareproton Nov 17 '14

No, it doesn't. That return value is a plain int, which you can't add to or subtract from a date. Yeah, you can use DATEADD; it quickly gets unreadable. A plain int is less human-readable than "03:51:21", which is what PostgreSQL returns if you ask it what 2014-01-04 14:53:21.000 - 2014-01-04 11:02:00.000 is (notice you can just do a subtraction instead of using a function).

MSDN says:

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.

So, using your method, it just breaks if you want second precision over a gap greater than 68 years, or millisecond precision over 25 days. In PostgreSQL, it just works properly. Do this:

select '150000-01-01 12:34:56.0001'::timestamp - '0001-09-12 03:34:12'::timestamp;

and you get this:

54785755 days 09:00:44.0001

8

u/jski Nov 17 '14 edited Jul 21 '24

angle busy tap label reply rock shy impossible rhythm badge

This post was mass deleted and anonymized with Redact

1

u/mycall Nov 17 '14

Does the same millisecond precision limitation exist for datetime2/datetimeoffset?

4

u/squareproton Nov 17 '14

I think so, yes. Here's the MSDN page: http://msdn.microsoft.com/en-us/library/ms189794.aspx

The limitation comes not from the date type but from the fact that DATEDIFF returns a signed 32-bit int, which only goes from approx -2.1bn to +2.1bn. So, 2.1bn milliseconds is your 25 days and 2.1bn seconds is the 68 years.