yoy.be "Why-o-Why"

postgreSQL: date and time manipulation

2016-05-13 00:06  pg1  coding  [permalink]

Babysteps! I've had the chance to dabble a bit in PostgreSQL, and after (years of) T-SQL, and some MySQL/MariaDB and SQLite, it's yet another SQL dialect to get a hang of. First impressions are good. It feels like a mature dialect. Searching the docs usually helps me find swiftly what I'm looking for. (And there's this ofcourse.) Porting an existing project makes you bump into the differences:

I'll probably hit a lot more like these in the time to come, but that you should use timestamp (or timestamptz) instead of datetime (or datetime2) deserves a separate mention. Especially there's no datediff, dateadd and datepart.

Instead of datepart, there's extract, that's pretty straight-forward, but to replace datediff and dateadd, you do something like it feels it should always have been: plain arithmetic.

PostgreSQL is pretty intelligent about operators (just do select * from pg_operator, wow!) so just subtract two timestamps to get something of type interval,  something like x + interval '2 days' result into exactly what you'd expect, and for constructing, apparently casting is pretty smart, for example:

select now()+(X.X||'day')::interval from (values (0),(1),(2),(3),(4)) X(X)

One more: instead of datediff(dd,x,y)=0 just do this: x::date=y::date pretty obvious if you think about it. Like I said, PostgreSQL made a pretty good first impression.