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

5

u/squareproton Nov 17 '14

Sorry, these interval types you're describing are nothing like PostgreSQL's ones, which are first-class values you can do arithmetic on and use intuitively. For example if you subtract one date from another, an interval is returned. Datediff just counts boundaries crossed, it doesn't give you a usable value representing a duration that you can then do stuff with. As for casting, your solution seems to be "don't do it", which is not an option. What are you to do if someone gives you a couple of million rows of data with silly-format dates in it? Remember I'm coming at this from an analytics perspective, where flexibility and control are absolutely key.

3

u/rmxz Nov 17 '14 edited Nov 18 '14

Sorry, these interval types you're describing are nothing like PostgreSQL's ones

Or rather, Microsoft's date/time/interval types are nothing like the SQL Standard's; while Postgres's are extremely close.

I think Postgres's only differs from the standard in backward compatible ways. For example the standard requires that all components of an interval have the same sign (positive or negative), while Postgres allows different components to differ in sign (1 hour and -1 seconds). For another example, the SQL standard doesn't let you mix Year-Month intervals with Hours-Minute-Second intervals, while Postgres lets you.

In contrast, Microsoft's looks totally different from the SQL Standard.

3

u/EternalNY1 Nov 17 '14

Datediff just counts boundaries crossed, it doesn't give you a usable value representing a duration that you can then do stuff with.

It doesn't?

StartDate = 2014-01-04 11:02:00.000

End Date = 2014-01-04 14:53:21.000

SELECT DATEDIFF(second, StartDate, EndDate)

Result: 13881

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.