Category: SQL

Modificare l’ora di un campo DATETIME in MySQL

A volte capita di lavorare su codice che non è possibile modificare e quindi dover fare i salti mortali per ottenere quella che in realtà è una cosa semplicissima.
Nello specifico avevo bisogno di modificare un campo DATE di una tabella in un campo DATETIME, dato che per l’applicazione era necessario memorizzare anche l’ora precisa dell’operazione.

I dati necessari all’inserimento del record vengono raccolti da un form e poi passati ad una procedura automatica che, processando il form, recupera e inserisce nel database.

La modifica di questa procedura (una bestia di 400 righe di codice con minimo 10 livelli condizionali innestati) era fuori discussione.

DROP TRIGGER IF EXISTS `afterInsertOnMovimento`;
DELIMITER //
CREATE TRIGGER `afterInsertOnMovimento`
AFTER INSERT ON `movimento`
FOR EACH ROW
BEGIN
  UPDATE movimento SET `data`=CONCAT_WS(' ',DATE(`data`), CURTIME()) WHERE id=NEW.id;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `beforeInsertOnMovimento`;
DELIMITER //
CREATE TRIGGER `beforeInsertOnMovimento`
BEFORE INSERT ON `movimento`
FOR EACH ROW
BEGIN
  SET NEW.data=CONCAT_WS(' ',DATE(`data`), CURTIME());
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `beforeInsertOnMovimento`;
DELIMITER //
CREATE TRIGGER `beforeInsertOnMovimento`
BEFORE INSERT ON `movimento`
FOR EACH ROW
BEGIN
  SET NEW.data=CONCAT_WS(' ',DATE(NEW.`data`), CURTIME());
END
//
DELIMITER ;

Multiple Sum with different conditions in MySQL

Today I was working on my last project that involves some statistics on data. I had a table like this:

sample table
]1 My table, with time and data fields.

I needed the daily, monthly and yearly sum of the value field. The simplest approach was to have three different queries to retrieve the needed values:

--day
SELECT SUM(value) 
FROM table 
WHERE DATE(time)=CURDATE();
-- month
SELECT SUM(value) 
FROM table 
WHERE MONTH(time)=MONTH(CURDATE()) AND YEAR(time)=YEAR(CURDATE());
-- year
SELECT SUM(value) 
FROM table 
WHERE YEAR(time)=YEAR(CURDATE());

But I wasn’t satisfied. I wanted to have all three values using only one query. So I searched for this issue and this is the resulting query:

SELECT 
  SUM(CASE WHEN DATE(time)=CURDATE() 
    THEN value 
    ELSE 0 end) AS value_day,
  SUM(CASE WHEN MONTH(time)=MONTH(CURDATE()) AND YEAR(time)=YEAR(CURDATE()) 
    THEN value 
    ELSE 0 end) AS value_month,
  SUM(CASE WHEN YEAR(time)=YEAR(CURDATE()) 
    THEN value 
    ELSE 0 end) AS value_year
FROM table

Using the CASE construct, we increment independently the three values so that each row that satisfies the condition gets summed up.