TimeStamp field or Date and Time

Navigation
0

In a table where there is a TimeStamp type field that is used, for example, among other things, to know the sum of all the records of a particular date, that is, by doing: 

select sum(campo_valor) from tabla where cast(campo_fecha as date)='2016-06-15'

Is it better to use two fields one Date and another Time or cast is still the best option?.

Since I have noticed that my queries with cast can not be indexed (or maybe I am doing something wrong), I have tried with all the possible combinations of the fields of that table and the only way that my query is indexed and be faster for more than 1 second is using something like this: 

select (sum(campo_valor) from tabla where campo_fecha>='2016-06-15 00:00:00' and campo_fecha<=2016-06-15 23:59:59

Roberto Garcia

    
asked by Roberto Garcia 16.06.2016 в 07:31
source

2 answers

1

I do it in the following way, to have to go through the date only once: I pass the date without time and it takes from that date until the next one. 

SELECT SUM(CAMPO_VALOR) FROM TABLA
WHERE
CAMPO_FECHA >= :FECHA AND
CAMPO_FECHA < :FECHA + 1
    
answered by 20.10.2016 в 19:28
0

The reason why the first query does not go by indexes, is because a function is applied to the column that is compared with a constant, which turns the where into NON-SARGABLE.

That is, even if there is an index in date_field, when using the cast, it is forced to go through all the records to apply the function, so the index is ignored.

Fortunately, the majority of NON-SARGABLE wheres can be converted to SARGABLE, with a small manipulation, which you got in the second query, by removing the cast function from the column and handling a range of date times, and it is because the second query if you go by index.

Thus, you can continue to use the timestamp data type for this column, using the search for the second query.

    
answered by 18.06.2016 в 06:29
How to get the value of the table calling from php? Error adding references to my Web project