-1

I have sqlite3 database with stored temperatures in table like this:

Data|Adres|Temperatura

2019-01-06 19:00:17|28FF24E08416043D|12.6

2019-01-06 19:05:50|28FF24E08416043D|-1.0

2019-01-06 19:10:17|28FF24E08416043D|18.0

2019-01-06 19:15:17|28FF24E08416043D|-4.4

2019-01-06 19:20:16|28FF24E08416043D|-7.3

2019-01-06 20:50:18|28FF24E08416043D|-8.1

And I need get 2 values (min & max in this day) eg: -8,1 & 18,0 but this is not happening.

If i try:

SELECT data,min(temperatura) FROM czujniki 
    where adres='28FF24E08416043D' and data>='2019-01-06' and data<='2019-01-07'

I've got: 2019-01-06 19:05:50|-1.0

which is not true, because lower temperature in this day is -8,1 in the other hand, if i try max value, I've got 18,0 - OK But in another day I've only lower than zero values and in the min value is -9,9 and max is -10,0

I'm confused, how to do it correctly?

Lorelorelore
  • 3,335
  • 8
  • 29
  • 40
miszczu
  • 39
  • 5

1 Answers1

0

You have to cast temperatura to decimal:

SELECT data,min(CAST(temperatura as decimal)) FROM czujniki 
    where adres='28FF24E08416043D' and data>='2019-01-06' and data<='2019-01-07'

Of course it's better to redefine the column temperatura as decimal or numeric and not text to avoid these problems.

forpas
  • 160,666
  • 10
  • 38
  • 76