Frameworks are well and good, but sometimes they shield us from simple concepts that we, as developers, should know. Let me tell you a story about this theme, and what happened.
Continue reading “How to update a junction table?”
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:

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.