const date = new Date() if (timeunit === 'all') { } else if (timeunit === 'day') { regStart = formatDate(date) regEnd = formatDate(new Date(date.valueOf() + 1000 * 3600 * 24)) console.log(regStart, regEnd) } else if (timeunit === 'week') { // 이번주 월요일 ~ 일요일 regStart = getMonday(new Date()) regEnd = getMonday(new Date(date.valueOf() + 1000 * 3600 * 24 * 7)) } else if (timeunit === 'month') { // 이번달 1일 ~ 31일 regStart = formatDate(new Date(date.getFullYear(), date.getMonth(), 1)) regEnd = formatDate(new Date(date.getFullYear(), date.getMonth() + 1, 0)) }
function formatDate (date) { const d = new Date(date) let month = '' + (d.getMonth() + 1) let day = '' + d.getDate() const year = d.getFullYear() if (month.length < 2) month = '0' + month if (day.length < 2) day = '0' + day return [year, month, day].join('-') }
function getMonday (d) { d = new Date(d) const day = d.getDay() const diff = d.getDate() - day + (day === 0 ? -6 : 1) // adjust when day is sunday return new Date(d.setDate(diff)) }
월별 SELECT pv_cnt , uv_cnt , uv.conn_time FROM (SELECT sum(pv_cnt) AS pv_cnt , to_char(conn_time, 'YYYY-MM') AS conn_time FROM portal.bmt_pv_conn GROUP BY to_char(conn_time, 'YYYY-MM')) pv, (SELECT sum(uv_cnt) AS uv_cnt , to_char(conn_time, 'YYYY-MM') AS conn_time FROM portal.bmt_uv_conn GROUP BY to_char(conn_time, 'YYYY-MM')) uv WHERE pv.conn_time = uv.conn_time AND uv.conn_time BETWEEN $1 AND $2 ORDER BY pv.conn_time DESC
모든 일
SELECT pv_cnt, uv_cnt, uv.conn_time FROM (SELECT sum(pv_cnt) AS pv_cnt, to_char(conn_time, 'YYYY-MM-DD') AS conn_time FROM portal.bmt_pv_conn GROUP BY conn_time) pv, (SELECT sum(uv_cnt) AS uv_cnt, to_char(conn_time, 'YYYY-MM-DD') AS conn_time FROM portal.bmt_uv_conn GROUP BY conn_time) uv WHERE pv.conn_time = uv.conn_time AND uv.conn_time BETWEEN $1 AND $2 ORDER BY pv.conn_time DESC
SELECT pv_cnt , uv_cnt , uv.conn_time FROM (SELECT sum(pv_cnt) AS pv_cnt , ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7) AS conn_time FROM portal.bmt_pv_conn GROUP BY ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7)) pv, (SELECT sum(uv_cnt) AS uv_cnt , ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7) AS conn_time FROM portal.bmt_uv_conn GROUP BY ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7)) uv WHERE pv.conn_time = uv.conn_time --AND uv.conn_time BETWEEN $1 AND $2 ORDER BY pv.conn_time DESC
SELECT pv_cnt , uv_cnt , uv.conn_time FROM (SELECT sum(pv_cnt) AS pv_cnt , date_trunc('week', conn_time) AS conn_time FROM portal.bmt_pv_conn GROUP BY date_trunc('week', conn_time)) pv, (SELECT sum(uv_cnt) AS uv_cnt , date_trunc('week', conn_time) AS conn_time FROM portal.bmt_uv_conn GROUP BY date_trunc('week', conn_time)) uv WHERE pv.conn_time = uv.conn_time -- AND uv.conn_time BETWEEN $1 AND $2 ORDER BY pv.conn_time DESC