[sqlite] weekly & month statistics query

 

 

 

운동 기록 records에서 주간별 볼륨합을 구해서 그래프로 변화를 표현하고자 한다.

(I'll calculate weekly & monthly volume sums and represent the changes in graph.)

 

records data set

---

[Weekly]

mysql:

SELECT DATE_FORMAT(DATE_SUB(today, INTERVAL (DAYOFWEEK(today)-1) DAY), '%Y/%m/%d') as start,
       DATE_FORMAT(DATE_SUB(today, INTERVAL (DAYOFWEEK(today)-7) DAY), '%Y/%m/%d') as end,
       DATE_FORMAT(`today`, '%Y%U') AS `date`,
       sum(volume) as volume
FROM records 
GROUP BY date;

를 sqlite로 변경을 해야했다.

(I've to change it to sqlite.)

 

sqlite:

SELECT strftime('%Y-%m-%d', datetime(today, strftime('%w', datetime(today, '-1 days'), 'localtime')), 'localtime') as start, 
	strftime('%Y-%m-%d', datetime(today, strftime('%w', datetime(today, '-7 days'), 'localtime')), 'localtime') as end, 
	strftime('%Y%U', today, 'localtime') as date, sum(volume) as volume 
FROM records 
GROUP BY date;

 

Result:

Leanmass application

 

---

[Monthly]

 

mysql:

SELECT MONTH(today) as date,
       sum(volume) as volume
FROM records
GROUP BY date;

 

to

 

sqlite:

SELECT	strftime('%m', today) as date, 
	sum(volume) as volume 
FROM records 
GROUP BY date;

 

 

Result:

Leanmass application

 

 

 

---

 

Reference site:

https://bluexmas.tistory.com/626

MySQL - 일별통계, 주간통계, 월간통계

https://javaexpert.tistory.com/180

MySQL과 SQLite 날짜 함수 비교 MySQL

https://codeday.me/ko/qa/20190525/625004.html

MySQL의 INTERVAL 및 UTC_TIMESTAMP에 상응하는 sqlite는 무엇입니까? - 코드 로그

http://blog.naver.com/PostView.nhn?blogId=hmw5233&logNo=60157560699&parentCategoryNo=&categoryNo=&viewDate=&isShowPopularPosts=false&from=postView

SQLite 에서 MySQL date_add(), date_sub() 함수 사용하기

 

http://www.nazuni.pe.kr/web/dev/mysql/functions_datetime.php

날짜와 시간 함수 :: MySQL :: 웹프로그래밍 :: Web 나쥬니랩

https://m.blog.naver.com/PostView.nhn?blogId=jya1216&logNo=50145934336&proxyReferer=https%3A%2F%2Fwww.google.com%2F

MySQL과 SQLite 날짜 함수 비교

https://stackoverflow.com/questions/650480/get-month-from-datetime-in-sqlite

Get month from DATETIME in sqlite