Create date sequence

R

First of month

date_seq <- seq(as.Date('2021-01-01'), as.Date('2022-01-01'), by = "month")
date_seq

##  [1] "2021-01-01" "2021-02-01" "2021-03-01" "2021-04-01" "2021-05-01"
##  [6] "2021-06-01" "2021-07-01" "2021-08-01" "2021-09-01" "2021-10-01"
## [11] "2021-11-01" "2021-12-01" "2022-01-01"

End of month

date_seq - 1

##  [1] "2020-12-31" "2021-01-31" "2021-02-28" "2021-03-31" "2021-04-30"
##  [6] "2021-05-31" "2021-06-30" "2021-07-31" "2021-08-31" "2021-09-30"
## [11] "2021-10-31" "2021-11-30" "2021-12-31"

Python

Python set-up

library('reticulate')
use_condaenv(condaenv = 'STOCK_MASTER', required = TRUE)

import pandas as pd

First of month

date_seq = pd.period_range('2021-01-01', '2022-01-01', freq='M')
date_seq.asfreq(freq='D', how='S')

## PeriodIndex(['2021-01-01', '2021-02-01', '2021-03-01', '2021-04-01',
##              '2021-05-01', '2021-06-01', '2021-07-01', '2021-08-01',
##              '2021-09-01', '2021-10-01', '2021-11-01', '2021-12-01',
##              '2022-01-01'],
##             dtype='period[D]')

End of month

date_seq = pd.period_range('2021-01-01', '2022-01-01', freq='M')
date_seq.asfreq(freq='D', how='E')

## PeriodIndex(['2021-01-31', '2021-02-28', '2021-03-31', '2021-04-30',
##              '2021-05-31', '2021-06-30', '2021-07-31', '2021-08-31',
##              '2021-09-30', '2021-10-31', '2021-11-30', '2021-12-31',
##              '2022-01-31'],
##             dtype='period[D]')

SQL - Postgres

Postgres set-up

library(DBI)
library(RPostgres)
library(jsonlite)

# Database connection
config <- jsonlite::read_json('C:/Users/brent/Documents/VS_Code/postgres/postgres/config.json')

con <- DBI::dbConnect(
  RPostgres::Postgres(),
  host      = 'localhost',
  port      = '5432',
  dbname    = 'stock_master_test',
  user      = 'postgres',
  password  = config$pg_password
)

First of month

select * from generate_series(timestamp '2021-01-01', '2022-01-01', '1 month') as first_of_month;

Displaying records 1 - 10

first_of_month
2021-01-01
2021-02-01
2021-03-01
2021-04-01
2021-05-01
2021-06-01
2021-07-01
2021-08-01
2021-09-01
2021-10-01

End of month

with fom as (
select * from generate_series(timestamp '2021-01-01', '2022-01-01', '1 month') as first_of_month
)
select first_of_month - interval '1 day' as end_of_month from fom;

Displaying records 1 - 10

end_of_month
2020-12-31
2021-01-31
2021-02-28
2021-03-31
2021-04-30
2021-05-31
2021-06-30
2021-07-31
2021-08-31
2021-09-30