How to subtract hours from a date sequentially in SQL?

Asked

Viewed 2,576 times

0

I have several fields with data, hora and sector:

Sector|Horas|Data
------|------|-----
Corte |01:00 |12/03/2019 17:00
EXP   |00:30 |12/03/2019 17:00
Etc

I want to subtract the time of the date, but always save the previous one as shown below:

Sector|Horas|Data             |Resultado
------|------|-----
Corte |01:00 |12/03/2019 17:00|12/03/2019 16:00
EXP   |00:30 |12/03/2019 17:00|12/03/2019 15:30
ETC

How can I fix this?

sql

  • How did you get to the result shown in the second line? It shouldn’t be 16:30?

  • What the SGBD that you are using?

  • I arrived at the result of the second line. Pk want to count from the end of the other result.

  • In this case the use of windows functions can help you. See the documentation of your DBMS.

1 answer

0

That depends on your SGBD, or commonly called, database.

Considering a table as:

CREATE TABLE TABELA (
  SECTOR VARCHAR(15) NOT NULL,
  HORAS TIME NOT NULL,
  DATA TIMESTAMP NOT NULL
);

Mysql

SELECT *, SUBTIME(DATA, HORAS) AS RESULTADO FROM TABELA;

SELECT *, TIMESTAMP(DATA - HORAS) AS RESULTADO FROM TABELA;

Postgresql

SELECT *, (DATA - HORAS) AS RESULTADO FROM TABELA;

MS SQL Server

Instead of TIMESTAMP, I used DATETIME. 2

SELECT *, (DATA - CONVERT(DATETIME, HORAS)) AS RESULTADO FROM TABELA;

SELECT *, DATEADD(MS, DATEDIFF(MS, HORAS, '00:00:00'), DATA) AS RESULTADO FROM TABELA; 3

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.