# 4 Tips for Working with Dates in PostgreSQL

DevFeed: [4 Tips for Working with Dates in PostgreSQL](<https://devfeed.tech/articles/4-tips-for-working-with-dates-in-postgresql-20368.md>)

Original publisher: [Read original article](<https://blog.sensible.io/2013/08/26/4-tips-for-working-with-dates-in-postgresql.html>)

Author: sensible.io team

Published: 2013-08-26T11:47:00Z

Content type: tutorial

Language: en

Sources: [Sensible](<https://devfeed.tech/sources/sensible.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Rails](<https://devfeed.tech/topics/rails.md>)

Tags: [documentation](<https://devfeed.tech/tags/documentation.md>), [operator](<https://devfeed.tech/tags/operator.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [rails](<https://devfeed.tech/tags/rails.md>), [time](<https://devfeed.tech/tags/time.md>), [timezone](<https://devfeed.tech/tags/timezone.md>)

## AI overview

A tutorial on working with dates and times in PostgreSQL. It explains current-time functions such as now() and clock_timestamp(), time intervals and arithmetic, date-part extraction, and timezone-aware operations, with SQL examples.

## Source excerpt

Those of us who come from Rails aren't surprised when we see something like 5.weeks.from_now or 3.days.ago + 2.hours, which makes working with dates much easier. But PostgreSQL got your back on this as well, you can just use the builtin functions and get most of the same functionality. Current Time/Date/Timestamp There are many ways of getting a current time, but first we need to distinguish between two types always returns current value (clock_timestamp()) always returns current value, unless in a transaction, in which case it returns the value from the beginning of the transaction (now()) Let's take a look at an example postgres=# BEGIN; postgres=# SELECT now(); now ------------------------------- 2013-08-26 12:17:43.182331+02 postgres=# SELECT now(); now ------------------------------- 2013-08-26 12:17:43.182331+02 postgres=# SELECT clock_timestamp(); clock_timestamp ------------------------------- 2013-08-26 12:17:50.698413+02 postgres=# SELECT clock_timestamp(); clock_timestamp ------------------------------- 2013-08-26 12:17:51.123905+02 As you can see, clock_timestamp() changes every time the statement is executed, but now() always returns the same value. It's also worth noting that both of these functions take timezone into account. Time interval, aka 3.days.ago You can easily create time intervals using the interval operator, for example interval '1 day' interval '5 days' interval '5 days' + interval '3 hours' interval '5 days 3 hours' As you can see, we can do simple math using the interval operator, which makes it very easy to construct things like 3.days.ago just by doing the following postgres=# SELECT now() - interval '3 days'; ?column? ------------------------------- 2013-08-23 12:23:40.069717+02 Extracting the day of the week and more Sometimes you just want to know the day of the week for a given date, or the century, or just the day. PostgreSQL has an extract() function which does just this. Just to put this into context the examples were executed