2022-09-12 Shifting dates or timestamps in PostgreSQL

For today I only have a short and simple PostgreSQL tip. Recently, for the purpose of testing, I needed a user created more than a day ago. Instead of manually typing a timestamp, I created a user now and did this:

update users set created = created - '1 day'::interval where id = '...';

Alternatively, I could just insert the user record, setting created to current_timestamp - '1 day'::interval.

Head to the manual to learn about the exact input syntax for the interval type. Basically, you can use things like 1 day 2 hours 3 minutes etc. Also, you can omit the units in some cases (as well as the cast to the interval type), though I would recommend against it for code legibility reasons.

That’s it for today. See you next week!

CategoryEnglish, CategoryBlog, CategoryPostgreSQL