samedi 30 janvier 2016

How to get first and last record of every month in Sqlite

in my java application I have stored candlestick data in a sqlite database (I'm new in sqlite language). The database contains one table for each stock with columns: date,open,high,low,close. In order to display monthly candles I need a ResultSet storing first and last value for every month, max value of high and min value of low. I was able to retrieve last two data by:

SELECT STRFTIME("%Y-%m", date/1000,'unixepoch', 'localtime') AS Date, max(high) AS High, min(low) AS Low

For the first needed data I tried to reach records :

where strftime('%m', date/1000, 'unixepoch', 'localtime')<>strftime('%m', date/1000, 'unixepoch', 'localtime','+1 day')

but if the last record in month stops for example in 2015-10-29 the query skips obviously this month because +1 day is 2015-10-30

How can I make it correctly? Thanks to all

Aucun commentaire:

Enregistrer un commentaire