Which is the index for a query substr on a date type?

Question

Hi I'm trying to do a query to print the users which do an action on a specific month on ORACLE SQL DEVELOPER.

Here are the tables:

CREATE TABLE USUARIO2 (
    CODIGO NUMBER(10) CONSTRAINT USUARIO_PK PRIMARY KEY,
    NOMBRE VARCHAR(50) CONSTRAINT NOM_USUARIO_NN NOT NULL,
    TELEFONO VARCHAR2(12),
    DIRECCION VARCHAR2(100)
);

CREATE TABLE SACA (
    COD_USUARIO NUMBER(10),
    COD_EJEMPLAR NUMBER(10),
    FECHA_PRES DATE DEFAULT SYSDATE,
    FECHA_DEV DATE,
    CONSTRAINT SACA_PK PRIMARY KEY (COD_USUARIO, COD_EJEMPLAR, FECHA_PRES),
    CONSTRAINT USUARIO_FK FOREIGN KEY (COD_USUARIO) REFERENCES USUARIO2(CODIGO) ON DELETE CASCADE,
    CONSTRAINT EJEMPLAR_FK FOREIGN KEY (COD_EJEMPLAR) REFERENCES EJEMPLAR(CODIGO) ON DELETE CASCADE
);

Here are the values of SACA:

INSERT INTO SACA VALUES (123,778899, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (123,112233, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (234,882143,'04-01-2017','04-07-2017');
INSERT INTO SACA VALUES (234,777722,'04-01-2017','04-07-2017');
INSERT INTO SACA VALUES (345,976543, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (456,446733,'04-01-2017','04-07-2017');
INSERT INTO SACA VALUES (567,778899, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (890,222266,'04-01-2017','04-07-2017');

And this is the query. i would like to query the users which get a book on a specific month (05) from column FECHA_DEV. So here is the query:

SELECT usuario2.nombre
FROM usuario2
JOIN saca ON usuario2.codigo=saca.cod_usuario WHERE SUBSTR(FECHA_DEV,3,5) IN ('05');

The query is empty, but if a change the values, and ask from the first and last position it works.

¿So which are the index of the month for the DATE format?

Answer 1

I am assuming Oracle, because of the use of the number data type and the sysdate reference.

---

Don't use string functions on DATE values. It is going to fail at one point or the other (e.g. on my computer, SUBSTR(sysdate,3,5) returns -MAR- not 03).

It's better to extract the month as a number.

So to get all rows where FECHA_DEV is in May, you should use:

SELECT usuario2.nombre
FROM usuario2
   JOIN saca ON usuario2.codigo=saca.cod_usuario 
WHERE extract(month from FECHA_DEV) = 5;

The above would require an index like this:

create index idx_fecha_dev_month on SACA  ( extract(month from FECHA_DEV)  );

Whether the index would be used is a different question. You will have to check the execution plan to verify it.

Answer 2

¿So which are the index of the month for the DATE format?

The premise of the question is flawed as a DATE is formatless as it is stored internally as 7 bytes (respectively: century, year-of-century, month, day-of-month, hour, minute and second) so there is no universal string representation of a DATE data type.

The SUBSTR( string, start_character, length ) function takes:

• A string as a its first argument;
• The index starting character of the sub-string is the second argument and the first character in the string is at index 1 (and not 0); and
• The length of the returned sub-string as its third argument.

So the first errors are that, if you are assuming the format DD-MM-RR and want a 2-character long sub-string starting from the 4th character of the string then the 2nd and 3rd argument should be 4, 2 and not 3, 5.

The first thing Oracle will do when you pass a DATE as the first argument is to try to implicitly convert the DATE to a string and your function call is effectively:

SUBSTR(
  TO_CHAR(
    fecha_dev,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
  ),
  4,
  2
)

The default NLS_DATE_FORMAT depends on the NLS_TERRITORY setting so you will not get a consistent value between users in different parts of the world (i.e. in Spain the default is DD/MM/RR whereas in America it is DD-MON-RR and the month is displayed as characters and in Sweden it is RRRR-MM-DD and getting the 4th and 5th characters would give the last year digit and a hyphen).

Even if your users are all in the same territory there is no guarantee that they will not set their own session parameters as any user can issue a command to alter their session to change it. For example:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD HH24MISS MONRR';

(and, yes, that date format does get used in some settings)

So, you should never rely on implicit conversion of dates to strings as you cannot be certain that you will get a consistent format.

If you do want to use SUBSTR then you should explicitly convert the date to a string and specify the format model:

WHERE SUBSTR( TO_CHAR( fecha_dev, 'DD-MM-RR' ), 4, 2 ) IN ( '05' )

However, if you are going to do that then you can just skip the SUBSTR and just output the month:

WHERE TO_CHAR( fecha_dev, 'MM' ) = '05'

Or you can use EXTRACT:

WHERE EXTRACT( MONTH FROM fecha_dev ) = 5

Answer 3

In SUBSTR, last parameter is for length from position defined with second parameter :

WHERE SUBSTR(FECHA_DEV, 3, 2) IN ('05'); -- changed 5 to 2

However, you can use extract :

WHERE extract(month FROM FECHA_DEV) = 5

Answer 4

It looks like you want all records with FECHA_DEV in the month of May.

If you want May in all years, use

WHERE MONTH(FECHA_DEV) = 5

But that WHERE expression must evaluate the MONTH function for every row. So you may be able to use an index on that expression.

If you want records from May of a certain year use a range search.

WHERE FECHA_DEV >= '2017-05-01' AND FECHA_DEV < '2017-06-01'

Then an index on the DATEs in the FECHA_DEV column will help, as it will let the server do an index range scan.

Pro tip Try to avoid treating dates as character strings. SQL offers all sorts of cool date arithmetic you can use when you treat dates as DATE or DATETIME types.

To display DATE or DATETIME values, use DATE_FORMAT().

Answer 5

FECHA_DEV is a date column and you should not treat it like a string.
Use EXTRACT() to get the month:

WHERE EXTRACT(month FROM FECHA_DEV) = 5