Basic Functions

SELECT NOW();

today()

## [1] "2020-03-23"

as_date(now())

## [1] "2020-03-23"

SELECT current_time;

SELECT NOW()::DATE;

Addition and Subtraction

In R, durations are always stored in seconds. This can lead to unexpected results:

ymd(20000101) + dyears(1)

## [1] "2000-12-31"

More often periods are used:

ymd(20000101) + years(1)

## [1] "2001-01-01"

ymd(20000101) + period(year=1, month=1, day=1, hour=1, minute=5, second=15)

## [1] "2001-02-02 01:05:15 UTC"

In SQL, the behaviour is similar to R’s periods:

SELECT DATE '2000-01-01' + INTERVAL '1 YEAR 1 MONTH 1 DAY 1 HOUR 5 MINUTE 15 SECOND' AS new_date;

Note that this is a datetime. To cast it to date:

SELECT (DATE '2000-01-01' + INTERVAL '1 YEAR')::DATE;

span <- ymd(20000101) %--% ymd(20010101)
span

## [1] 2000-01-01 UTC--2001-01-01 UTC

as.period(span)

## [1] "1y 0m 0d 0H 0M 0S"

span / ddays(1)

## [1] 366

SELECT DATE '2001-01-01' - DATE '2000-01-01' AS span

SELECT AGE(DATE '2001-01-01', DATE '2000-01-01') AS span

Extracting Components

In lubridate, you use functions such as year and month on date/datetime to extract components. In PostgreSQL, you use the EXTRACT function with this pattern: EXTRACT(${COMPONENT} FROM date) where ${COMPONENT} can be one of those listed in the following table:

month(now())

## [1] 3

SELECT EXTRACT(MILLISECOND FROM NOW());

In PostgreSQL, DOW (day of week) starts from Sunday.