[PostgreSQL] 시작날짜 끝날짜 사이 중간값들 구하기

PostgreSQL: Auto generating a sample dataset (II)

Some time ago I presented a simple solution to generate date dimension data in PostgreSQL using standard SQL. The below approach is another take on this and probably a little bit easier to read then the previous approach.

This time I made use of the GENERATE_SERIES function. As a simple example, we first use a Common Table Expression to generate a dataset which holds 10 successive dates and then in the main query we extract some date information:

WITH date_series AS (

SELECT

DATE(GENERATE_SERIES(DATE '2012-01-01', DATE '2012-01-10','1 day')) AS date

)

SELECT 

date 

, EXTRACT(DAY FROM date) AS day

, EXTRACT(MONTH FROM date) AS month

, EXTRACT(QUARTER FROM date) AS quarter

, EXTRACT(YEAR FROM date) AS year

FROM 

date_series

PostgrQL: Auto gerting a sample dataset (II)

Some time ago I presented a simple solution to generate date dimension data in PostgreSQL using standard SQL. The below approach is another take on this and probably a little 

bit easier to read then the previous approach.

This time I made use of the GENERATE_SERIES function. As a simple example, we first use a Common Table Expression to generate a dataset which holds 10 successive 

dates and then in the main query we extract some date information:

WITH date_series AS (

SELECT

DATE(GENERATE_SERIES(DATE '2012-01-01', DATE '2012-01-10','1 day')) AS date

)

SELECT 

date 

, EXTRACT(DAY FROM date) AS day

, EXTRACT(MONTH FROM date) AS month

, EXTRACT(QUARTER FROM date) AS quarter

, EXTRACT(YEAR FROM date) AS year

FROM 

date_series

PostgreSQL: Auto generating a sample dataset (II)

Some time ago I presented a simple solution to generate date dimension data in PostgreSQL using standard SQL. The below approach is another take on this and probably a little bit easier to read then the previous approach.

This time I made use of the GENERATE_SERIES function. As a simple example, we first use a Common Table Expression to generate a dataset which holds 10 successive dates and then in the main query we extract some date information:

WITH date_series AS (

SELECT

DATE(GENERATE_SERIES(DATE '2012-01-01', DATE '2012-01-10','1 day')) AS date

)

SELECT 

date 

, EXTRACT(DAY FROM date) AS day

, EXTRACT(MONTH FROM date) AS month

, EXTRACT(QUARTER FROM date) AS quarter

, EXTRACT(YEAR FROM date) AS year

FROM 

date_series[PostgreSQL: Auto generating a sample dataset (II)

Some time ago I presented a simple solution to generate date dimension data in PostgreSQL using standard SQL. The below approach is another take on this and probably a little bit easier to read then the previous approach.

This time I made use of the GENERATE_SERIES function. As a simple example, we first use a Common Table Expression to generate a dataset which holds 10 successive dates and then in the main query we extract some date information:

WITH date_series AS (

SELECT

DATE(GENERATE_SERIES(DATE '2012-01-01', DATE '2012-01-10','1 day')) AS date

)

SELECT 

date 

, EXTRACT(DAY FROM date) AS day

, EXTRACT(MONTH FROM date) AS month

, EXTRACT(QUARTER FROM date) AS quarter

, EXTRACT(YEAR FROM date) AS year

FROM 

date_series출처] 시작날짜 끝날짜 사이 중간값들 구하기|작성자 얼리캣