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:
'f'instead of 1 and 0)
create procedure, but there's
create or replace function ... returns void
exec, but since it's all functions use
ID int identity(1,1) not null, use
ID serial primary key not nullor hook up a sequence by yourself...
insert into x (...) values (...) returning ID into NewID
I'll probably hit a lot more like these in the time to come, but that you should use
timestamptz) instead of
datetime2) deserves a separate mention. Especially there's no
extract, that's pretty straight-forward, but to replace
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.