postgreSQL: date and time manipulation

2016-05-13 00:06  pg1    [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:

• no isnull, use coalesce
• no bit, use bool (and 't' and 'f' instead of 1 and 0)
• no create procedure, but there's create or replace function ... returns void
• no exec,  but since it's all functions use select (with into!)
• instead of ID int identity(1,1) not null, use ID serial primary key not null or hook up a sequence by yourself...
• no @@identity, but insert into x (...) values (...) returning ID into NewID
• etc.

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.

→