El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Funcions de data i hora

Següent ►
►

De totes les preguntes que es fa la direcció de BotigaVerda, la majoria porten una data a dins: quant hem facturat aquest mes?, quins clients no compren des de fa mig any?, quants dies va trigar a arribar aquella devolució?, creixem trimestre a trimestre? Fins ara només sabies comparar dates amb >= i <. Aquesta lliçó converteix la columna data_comanda en una eina d'anàlisi.

També salda el deute de 04-05, on EXTRACT va aparèixer de passada per agrupar per any i es va deixar explicat "per al mòdul 6". I porta la peça que faltava per als informes: DATE_TRUNC, la funció que converteix 20 comandes soltes en una sèrie mensual.

Nota sobre la reproductibilitat: els exemples que necessiten "avui" fan servir la data fixa DATE '2026-03-01' en lloc de CURRENT_DATE, perquè els resultats que veus aquí coincideixin amb els teus. En producció faries servir CURRENT_DATE.

Contingut

  1. Els tipus temporals, i per què BotigaVerda fa servir DATE
  2. L'"ara": CURRENT_DATE, NOW() i CLOCK_TIMESTAMP()
  3. Aritmètica de dates, INTERVAL i AGE()
  4. EXTRACT i DATE_PART
  5. DATE_TRUNC: el patró de l'informe temporal
  6. Formatar amb TO_CHAR, analitzar amb TO_DATE
  7. Informes sense buits amb generate_series
  8. Taula comparativa per motor
  9. Errors habituals i consells
  10. Exercicis
  11. Conclusió

  1. Els tipus temporals, i per què BotigaVerda fa servir DATE

Tipus Què desa Exemple de literal Mida
DATE Una data de calendari, sense hora DATE '2025-03-04' 4 bytes
TIME Una hora del dia, sense data TIME '18:30:00' 8 bytes
TIMESTAMP Data + hora, sense zona horària TIMESTAMP '2025-03-04 18:30:00' 8 bytes
TIMESTAMPTZ Data + hora, amb zona horària TIMESTAMPTZ '2025-03-04 18:30:00+01' 8 bytes
INTERVAL Una durada, no un instant INTERVAL '30 days' 16 bytes

BotigaVerda fa servir DATE a totes les seves columnes temporals —data_comanda, data_registre, data_alta, data_contractacio, ressenyes.data, devolucions.data— perquè en aquest model són fets de calendari: una comanda és "del 4 de març", no "de les 18:47:03 del 4 de març". Desar una hora que ningú no farà servir afegeix ambigüitat sense aportar informació.

Què canviaria amb marques de temps

En una botiga real voldries l'hora: per mesurar el temps de preparació, per saber a quina hora es compra més, per auditar qui va canviar què i quan. I tan bon punt hi afegeixes l'hora, apareix la zona horària:

Situació Amb DATE Amb TIMESTAMP Amb TIMESTAMPTZ
Comanda a les 23:50 a Lió (CET) "4 de març", i prou 2025-03-04 23:50 — hora d'on? Instant inequívoc; es mostra en la zona de cada usuari
Comparar amb una comanda de Lisboa (WET) Directe Incomparable: una hora de diferència invisible Correcte: el motor normalitza
Canvi d'hora d'octubre El problema no existeix Dos instants amb la mateixa representació Resolt
data >= '2025-03-01' AND data < '2025-04-01' Exacte Exacte Depèn de la zona de sessió

La recomanació professional: en una aplicació real, TIMESTAMPTZ és gairebé sempre l'elecció correcta. TIMESTAMP sense zona sembla més senzill i és una trampa: desa un número sense dir a quin rellotge pertany, i el dia que tinguis usuaris a dos països ja no hi ha manera de saber-ho. TIMESTAMPTZ desa internament un instant en UTC i el converteix a la zona de cada sessió en llegir-lo. I DATE és correcte només quan la dada és de debò una data de calendari: una data de naixement, un dia de facturació, un venciment.

Aquest és exactament el criteri pel qual BotigaVerda fa servir DATE, i pel qual la teva propera aplicació probablement no ho hauria de fer.

  1. L'"ara": CURRENT_DATE, NOW() i CLOCK_TIMESTAMP()

Funció Què retorna Quan s'avalua
CURRENT_DATE / CURRENT_TIME DATE d'avui / TIME WITH TIME ZONE Inici de la transacció
CURRENT_TIMESTAMP TIMESTAMPTZ Inici de la transacció
NOW() Idèntica a CURRENT_TIMESTAMP Inici de la transacció
STATEMENT_TIMESTAMP() TIMESTAMPTZ Inici de la sentència
CLOCK_TIMESTAMP() TIMESTAMPTZ En l'instant de la crida

La diferència entre NOW() i CLOCK_TIMESTAMP() sorprèn la primera vegada. Si obres BEGIN;, executes SELECT NOW(), CLOCK_TIMESTAMP();, esperes uns segons i repeteixes la consulta abans del COMMIT, la primera columna retorna exactament el mateix valor les dues vegades i la segona no. NOW() està congelada durant tota la transacció, i això és una garantia deliberada: si un procés insereix cent files amb NOW(), les cent comparteixen la mateixa marca i formen un lot identificable. Per mesurar quant triga una cosa dins d'una transacció, NOW() no serveix: necessites CLOCK_TIMESTAMP().

Conseqüència pràctica: data_registre DATE NOT NULL DEFAULT CURRENT_DATE (el DEFAULT de clients a 01-06) és estable i correcte. Un DEFAULT clock_timestamp() seria volàtil i, com vas veure a 05-06, obligaria a reescriure la taula sencera en afegir la columna.

  1. Aritmètica de dates, INTERVAL i AGE()

Operació Tipus del resultat Exemple Resultat
date - date INTEGER (dies) DATE '2026-02-21' - DATE '2025-03-04' 354
date + enter DATE DATE '2025-03-04' + 30 2025-04-03
date + INTERVAL TIMESTAMP DATE '2025-03-04' + INTERVAL '30 days' 2025-04-03 00:00:00
timestamp - timestamp INTERVAL — 1 day 02:15:00
AGE(a, b) INTERVAL llegible AGE(DATE '2026-03-01', DATE '2025-01-10') 1 year 1 mon 19 days
AGE(x) INTERVAL des d'avui AGE(data_registre) equival a AGE(CURRENT_DATE, data_registre)

Dues trampes en aquesta taula. date - date dona un enter i timestamp - timestamp dona un interval: el mateix signe - amb dues semàntiques, de manera que migrar una columna de DATE a TIMESTAMP canvia el tipus de totes les teves restes en silenci. I date + INTERVAL retorna un TIMESTAMP, no un DATE; si necessites una data, afegeix-hi ::DATE.

INTERVAL i les seves unitats

SELECT DATE '2025-01-31' + INTERVAL '1 month'  AS fi_de_gener,
       DATE '2025-03-04' + INTERVAL '2 weeks'  AS dues_setmanes,
       DATE '2026-02-21' - INTERVAL '6 months' AS fa_mig_any;
fi_de_gener dues_setmanes fa_mig_any
2025-02-28 00:00:00 2025-03-18 00:00:00 2025-08-21 00:00:00

Fixa't en la primera: 31 de gener + 1 mes = 28 de febrer, perquè el 31 de febrer no existeix i PostgreSQL ajusta a l'últim dia del mes. Un mes no és una quantitat fixa de dies: INTERVAL '1 month' i INTERVAL '30 days' són coses diferents.

AGE(): l'antiguitat llegible

AGE no retorna dies: retorna anys, mesos i dies, com ho diria una persona.

SELECT id, CONCAT_WS(' ', nom, cognoms) AS client, data_registre,
       DATE '2026-03-01' - data_registre     AS dies,
       AGE(DATE '2026-03-01', data_registre) AS antiguitat
FROM clients WHERE id IN (1, 4, 7, 12, 15) ORDER BY id;
id client data_registre dies antiguitat
1 Lucía Martínez Soler 2025-01-10 415 1 year 1 mon 19 days
4 Javier Ortega Ruiz 2025-02-14 380 1 year 15 days
7 Sofia Moreira Costa 2025-03-21 345 11 mons 8 days
12 Diego Ramos Herrera 2025-06-01 273 9 mons
15 Inés Carrasco Vega 2026-01-08 52 1 mon 21 days

Dos detalls: PostgreSQL omet els components que valen zero (en Javier no mostra "0 mons"; en Diego, ni mesos ni dies), i AGE és el que vols per mostrar a una persona mentre que la resta en dies és el que vols per ordenar i comparar.

Cas: dies entre la comanda i la devolució

SELECT d.id AS devolucio, d.comanda_id, co.data_comanda,
       d.data                    AS data_devolucio,
       d.data - co.data_comanda  AS dies_transcorreguts,
       d.import
FROM devolucions AS d
JOIN comandes    AS co ON co.id = d.comanda_id
ORDER BY d.id;
devolucio comanda_id data_comanda data_devolucio dies_transcorreguts import
1 6 2025-05-23 2025-05-25 2 26.75
2 10 2025-08-03 2025-08-11 8 34.02
3 13 2025-10-22 2025-10-30 8 19.80

Les tres devolucions de BotigaVerda: la de la comanda cancel·lada es va tramitar en 2 dies i les dues per incidència de producte, en 8. Amb TIMESTAMP en lloc de DATE aquesta resta retornaria un INTERVAL com ara 8 days 03:12:00, més precís i menys còmode d'agregar.

  1. EXTRACT i DATE_PART

EXTRACT(camp FROM data) treu un component. DATE_PART('camp', data) fa el mateix amb sintaxi de funció; EXTRACT és l'estàndard SQL i és la forma preferible.

Camp Què retorna Per a DATE '2025-03-04'
YEAR Any 2025
MONTH Mes, 1-12 3
DAY Dia del mes 4
QUARTER Trimestre, 1-4 1
WEEK Setmana ISO, 1-53 10
DOW / ISODOW Dia de la setmana: 0 = diumenge / 1 = dilluns 2 / 2
DOY Dia de l'any, 1-366 63
EPOCH Segons des de 1970-01-01 1741046400 
SELECT id, data_comanda,
       EXTRACT(YEAR FROM data_comanda)    AS any_,
       EXTRACT(MONTH FROM data_comanda)   AS mes,
       EXTRACT(QUARTER FROM data_comanda) AS trimestre,
       EXTRACT(DOW FROM data_comanda)     AS dow,
       EXTRACT(WEEK FROM data_comanda)    AS setmana_iso
FROM comandes WHERE id IN (1, 8, 13, 17, 20) ORDER BY id;
id data_comanda any_ mes trimestre dow setmana_iso
1 2025-03-04 2025 3 1 2 10
8 2025-06-28 2025 6 2 6 26
13 2025-10-22 2025 10 4 3 43
17 2026-01-13 2026 1 1 2 3
20 2026-02-21 2026 2 1 6 8

Tres avisos: DOW comença en diumenge amb el 0 (convenció de C, no europea); WEEK és la setmana ISO, de manera que els primers dies de gener poden pertànyer a la setmana 52 o 53 de l'any anterior; i EXTRACT retorna NUMERIC des de PostgreSQL 14. I el de rendiment, ja conegut: WHERE EXTRACT(YEAR FROM data_comanda) = 2025 funciona però embolcalla la columna en una funció i no aprofita l'índex; la forma indexable és la de 02-03, WHERE data_comanda >= '2025-01-01' AND data_comanda < '2026-01-01'. Ho mesuraràs a 08-03.

  1. DATE_TRUNC: el patró de l'informe temporal

DATE_TRUNC(unitat, data) rebaixa la data a l'inici de la unitat demanada: el 22 d'octubre truncat a mes és l'1 d'octubre. Aquesta és tota la idea, i és la base de gairebé tot informe temporal.

Crida Resultat per a 2025-10-22
DATE_TRUNC('day', d) 2025-10-22 00:00:00
DATE_TRUNC('week', d) 2025-10-20 00:00:00 (dilluns ISO)
DATE_TRUNC('month', d) 2025-10-01 00:00:00
DATE_TRUNC('quarter', d) 2025-10-01 00:00:00
DATE_TRUNC('year', d) 2025-01-01 00:00:00

Compte amb el tipus: encara que li passis un DATE, DATE_TRUNC retorna un TIMESTAMP i per això hi veus el 00:00:00. Perquè l'informe mostri una data neta, afegeix-hi ::DATE.

Facturació mensual

SELECT DATE_TRUNC('month', co.data_comanda)::DATE AS mes,
       COUNT(DISTINCT co.id)                        AS comandes,
       COUNT(*)                                     AS linies,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio
FROM comandes       AS co
JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP BY DATE_TRUNC('month', co.data_comanda) ORDER BY mes;
mes comandes linies facturacio
2025-03-01 2 5 68.80
2025-04-01 2 5 61.28
2025-05-01 2 5 58.85
2025-06-01 2 5 95.48
2025-07-01 1 3 44.60
2025-08-01 1 2 48.27
2025-09-01 1 2 32.76
2025-10-01 2 5 97.20
2025-11-01 1 3 31.70
2025-12-01 2 5 64.58
2026-01-01 2 4 75.10
2026-02-01 2 3 49.33

12 mesos, 20 comandes, 47 línies i 727,95 € sumant l'última columna: la xifra canònica del mòdul 4, ara desglossada. I els anys quadren: els deu primers mesos sumen 603,52 € (2025) i els dos últims, 124,43 € (2026). El millor mes és l'octubre del 2025 amb 97,20 €; el pitjor, el novembre del 2025 amb 31,70 €.

Per trimestre

SELECT DATE_TRUNC('quarter', co.data_comanda)::DATE AS trimestre,
       COUNT(DISTINCT co.id)                          AS comandes,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio
FROM comandes       AS co
JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP BY DATE_TRUNC('quarter', co.data_comanda) ORDER BY trimestre;
trimestre comandes facturacio
2025-01-01 2 68.80
2025-04-01 6 215.61
2025-07-01 3 125.63
2025-10-01 5 193.48
2026-01-01 4 124.43

DATE_TRUNC enfront d'EXTRACT per agrupar: totes dues funcionen però no són equivalents. EXTRACT(MONTH FROM d) retorna 3 per al març de qualsevol any, així que el març del 2025 i el del 2026 caurien al mateix grup; DATE_TRUNC('month', d) conserva l'any i ordena cronològicament sense trucs. EXTRACT per a estacionalitat, DATE_TRUNC per a sèries temporals.

  1. Formatar amb TO_CHAR, analitzar amb TO_DATE

TO_CHAR(data, patro) converteix una data en text amb el format que vulguis.

Patró Què produeix Per a 2025-03-04
'YYYY-MM-DD' ISO 2025-03-04
'DD/MM/YYYY' Format català 04/03/2025
'YYYY-MM' Any i mes, ordenable com a text 2025-03
'YYYY"-Q"Q' Any i trimestre (text literal entre cometes) 2025-Q1
'Month' / 'FMMonth' Mes en anglès, amb i sense farciment a 9 caràcters March / March
'TMMonth' Mes traduït segons lc_time, sense farciment Març
'TMDay' Dia de la setmana traduït Dimarts
'IW' / 'Q' Setmana ISO / trimestre 10 / 1

Perquè la TM funcioni en català cal fixar la configuració regional de la sessió:

SET lc_time = 'ca_ES.UTF-8';

SELECT DATE_TRUNC('month', data_comanda)::DATE AS mes,
       TO_CHAR(data_comanda, 'YYYY-MM')        AS periode,
       TO_CHAR(data_comanda, 'DD/MM/YYYY')     AS data_ca,
       TO_CHAR(data_comanda, 'TMMonth YYYY')   AS mes_llarg,
       TO_CHAR(data_comanda, 'TMDay')          AS dia_setmana
FROM comandes WHERE id IN (1, 13, 20) ORDER BY id;
mes periode data_ca mes_llarg dia_setmana
2025-03-01 2025-03 04/03/2025 Març 2025 Dimarts
2025-10-01 2025-10 22/10/2025 Octubre 2025 Dimecres
2026-02-01 2026-02 21/02/2026 Febrer 2026 Dissabte

Regla d'or: formata només en presentar. TO_CHAR(d, 'DD/MM/YYYY') produeix text, i el text s'ordena alfabèticament: '01/12/2025' aniria abans que '04/03/2025'. Si necessites ordenar o agrupar, fes-ho per la data i formata després. L'única excepció segura és 'YYYY-MM', que sí que ordena bé com a text perquè és ISO.

TO_DATE(text, patro) fa el camí invers, i és on viu l'ambigüitat clàssica:

SELECT TO_DATE('03/04/2025', 'DD/MM/YYYY') AS interpretacio_europea,
       TO_DATE('03/04/2025', 'MM/DD/YYYY') AS interpretacio_americana;
interpretacio_europea interpretacio_americana
2025-04-03 2025-03-04

El mateix text, dues dates diferents, cap de les dues incorrecta. Per això el patró és obligatori i per això el format ISO AAAA-MM-DD és l'únic que no s'interpreta malament mai. Quan importis un CSV, exigeix el format a l'especificació i no el dedueixis de les dades: '03/04/2025' no et dirà quin és.

  1. Informes sense buits amb generate_series

Aquí es cobra la promesa de 03-06 i de 06-02. Un GROUP BY només retorna els grups que existeixen a les dades: si en un mes no hi va haver cap alta, aquell mes no apareix i l'informe menteix per omissió. Les altes de clients per mes, tal com surten del GROUP BY, donen 8 files per a 13 mesos d'història. La solució és fabricar la sèrie completa de mesos i unir-la per l'esquerra:

SELECT s.mes::DATE      AS mes,
       COUNT(c.id)      AS altes
FROM generate_series(DATE '2025-01-01', DATE '2026-01-01', INTERVAL '1 month') AS s(mes)
LEFT JOIN clients AS c
       ON DATE_TRUNC('month', c.data_registre) = s.mes
GROUP BY s.mes
ORDER BY s.mes;
mes altes
2025-01-01 2
2025-02-01 3
2025-03-01 2
2025-04-01 2
2025-05-01 2
2025-06-01 2
2025-07-01 0
2025-08-01 0
2025-09-01 1
2025-10-01 0
2025-11-01 0
2025-12-01 0
2026-01-01 1

13 files i 15 clients. Ara es veu el que el GROUP BY amagava: BotigaVerda va captar clients amb regularitat fins al juny del 2025 i es va assecar durant el segon semestre —cinc mesos amb zero altes—. Aquesta és una conclusió de negoci que senzillament no existia a l'informe de 8 files.

Tres detalls fan que el patró funcioni: el LEFT JOIN va de la sèrie a les dades i mai a l'inrevés; es compta COUNT(c.id) i no COUNT(*), perquè COUNT(*) comptaria la fila fabricada pel LEFT JOIN i donaria 1 on ha de donar 0 (04-04); i generate_series amb INTERVAL '1 month' genera timestamps, que el ::DATE neteja i el DATE_TRUNC de l'ON fa comparables. El mateix esquema serveix per a dies de la setmana, trimestres o l'eix horari d'un tauler: és el patró d'informe més reutilitzable del curs.

  1. Taula comparativa per motor

Tasca PostgreSQL 16 MySQL 8 SQLite SQL Server Oracle
Data d'avui CURRENT_DATE CURDATE() DATE('now') CAST(GETDATE() AS DATE) TRUNC(SYSDATE)
Instant actual NOW(), CURRENT_TIMESTAMP NOW() DATETIME('now') SYSDATETIME() SYSTIMESTAMP
Diferència en dies d2 - d1 DATEDIFF(d2, d1) JULIANDAY(d2) - JULIANDAY(d1) DATEDIFF(day, d1, d2) d2 - d1
Sumar dies d + 30 DATE_ADD(d, INTERVAL 30 DAY) DATE(d, '+30 days') DATEADD(day, 30, d) d + 30
Extreure un component EXTRACT(YEAR FROM d) EXTRACT, YEAR(d) STRFTIME('%Y', d) DATEPART(year, d) EXTRACT(YEAR FROM d)
Truncar a mes DATE_TRUNC('month', d) DATE_FORMAT(d, '%Y-%m-01') DATE(d, 'start of month') DATETRUNC(month, d) (2022+) TRUNC(d, 'MM')
Formatar TO_CHAR(d, 'DD/MM/YYYY') DATE_FORMAT(d, '%d/%m/%Y') STRFTIME('%d/%m/%Y', d) FORMAT(d, 'dd/MM/yyyy') TO_CHAR(d, 'DD/MM/YYYY')
Analitzar text TO_DATE(t, 'DD/MM/YYYY') STR_TO_DATE(t, '%d/%m/%Y') — PARSE(t AS date …) TO_DATE(t, 'DD/MM/YYYY')
Tipus amb zona horària TIMESTAMPTZ TIMESTAMP (desa en UTC) no hi ha tipus data DATETIMEOFFSET TIMESTAMP WITH TIME ZONE
Sèrie de dates generate_series(d1, d2, '1 day') CTE recursiva CTE recursiva GENERATE_SERIES (2022+) CONNECT BY LEVEL

Dos advertiments valen per tota la taula. SQLite no té tipus de data: desa text, números julians o segons epoch, i totes les seves funcions són STRFTIME sobre text; funciona, però res no impedeix ficar '04/03/2025' a la mateixa columna. I el DATEDIFF de MySQL i el de SQL Server porten els arguments en ordre diferent —DATEDIFF(d2, d1) enfront de DATEDIFF(day, d1, d2)—, de manera que una migració descurada canvia el signe de tots els resultats.

I l'error que cal evitar en qualsevol motor: desar dates com a text. Una columna VARCHAR(10) amb '2025-03-04' accepta '2025-13-45', no es pot sumar ni restar, s'ordena malament tan bon punt algú escrigui '4/3/2025' i no pot fer servir un índex de rang amb eficàcia. Si la dada és una data, el tipus és DATE.

Errors habituals i consells

  • Desar dates com a text. L'error d'arrel. DATE valida, ordena, resta i indexa; VARCHAR no fa res d'això.
  • Fer servir TIMESTAMP sense zona en una aplicació amb usuaris de diversos països. TIMESTAMPTZ és l'elecció per omissió.
  • Esperar que date + INTERVAL '1 day' retorni un DATE. Retorna TIMESTAMP, igual que DATE_TRUNC: d'aquí els 00:00:00 dels informes. Afegeix-hi ::DATE.
  • Creure que INTERVAL '1 month' són 30 dies. 2025-01-31 + 1 mes és 2025-02-28. I no confonguis DOW (diumenge = 0) amb ISODOW (dilluns = 1).
  • Agrupar per EXTRACT(MONTH …) en una sèrie de diversos anys. El març del 2025 i el març del 2026 cauen al mateix grup. Per a sèries temporals, DATE_TRUNC.
  • Filtrar amb EXTRACT(YEAR FROM d) = 2025. Correcte però no indexable. Fes servir >= '2025-01-01' AND < '2026-01-01' (02-03).
  • Ordenar per una data ja formatada amb TO_CHAR. S'ordena com a text. Ordena per la data i formata després; 'YYYY-MM' és l'única excepció segura.
  • Interpretar '03/04/2025' sense especificar el patró. Són dues dates diferents segons el país.
  • Comptar amb COUNT(*) en un informe amb generate_series. Compta la fila fabricada pel LEFT JOIN; compta una columna de la taula de dades.
  • Consell: prefereix >= inici AND < fi a BETWEEN en dates. Amb DATE funcionen igual, però el dia que la columna passi a TIMESTAMP, BETWEEN perdrà tot l'últim dia llevat de les 00:00.
  • Consell: desa en UTC i converteix en presentar, i per depurar fixa una data de referència (DATE '2026-03-01') en comptes de CURRENT_DATE: els teus resultats seran reproduïbles demà.

Exercicis

Exercici 1

Direcció vol l'informe mensual del 2025 complet. Per a cadascun dels dotze mesos —encara que no hi hagi res— mostra el mes en format YYYY-MM, el nombre de comandes i la facturació de producte. Fes servir generate_series i explica per què el gener i el febrer apareixen a zero.

Exercici 2

Màrqueting vol mesurar la velocitat de conversió: quants dies passen entre el registre d'un client i la seva primera comanda. Per als clients 1 a 8, mostra el nom complet, la data_registre, la data de la primera comanda i els dies transcorreguts, ordenat per dies. (Pista: MIN(data_comanda) agrupant per client.)

Exercici 3

Un company ha escrit aquest informe d'estacionalitat i afirma que "el març és el nostre mes més fluix":

-- ⚠️ Sospitosa
SELECT EXTRACT(MONTH FROM data_comanda) AS mes, COUNT(*) AS comandes
FROM comandes GROUP BY EXTRACT(MONTH FROM data_comanda) ORDER BY mes;
  1. Què està mesurant realment i per què la conclusió és fràgil amb les dades de BotigaVerda?
  2. Escriu la versió amb DATE_TRUNC que respon a "com evoluciona el negoci mes a mes?".
  3. En quin cas sí que seria correcta la consulta original?

Solucions

Solució 1

SELECT TO_CHAR(s.mes, 'YYYY-MM')                                           AS mes,
       COUNT(DISTINCT co.id)                                               AS comandes,
       COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari
                          * (1 - lc.descompte)), 2), 0)                    AS facturacio
FROM generate_series(DATE '2025-01-01', DATE '2025-12-01', INTERVAL '1 month') AS s(mes)
LEFT JOIN comandes       AS co ON DATE_TRUNC('month', co.data_comanda) = s.mes
LEFT JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP BY s.mes
ORDER BY s.mes;
mes comandes facturacio
2025-01 0 0.00
2025-02 0 0.00
2025-03 2 68.80
2025-04 2 61.28
2025-05 2 58.85
2025-06 2 95.48
2025-07 1 44.60
2025-08 1 48.27
2025-09 1 32.76
2025-10 2 97.20
2025-11 1 31.70
2025-12 2 64.58

12 files, 16 comandes i 603,52 €: la facturació del 2025 del mòdul 4. El gener i el febrer surten a zero perquè la primera comanda de BotigaVerda és del 4 de març del 2025: la botiga existia però encara no venia. Sense generate_series aquests dos mesos no apareixerien i un gràfic de línies començaria al març, donant a entendre que no hi ha història anterior. El COALESCE (06-04) converteix el NULL de SUM sobre un conjunt buit en 0.00.

Solució 2

SELECT c.id, CONCAT_WS(' ', c.nom, c.cognoms) AS client, c.data_registre,
       MIN(co.data_comanda)                    AS primera_comanda,
       MIN(co.data_comanda) - c.data_registre  AS dies
FROM clients  AS c
JOIN comandes AS co ON co.client_id = c.id
WHERE c.id <= 8
GROUP BY c.id, c.nom, c.cognoms, c.data_registre ORDER BY dies;
id client data_registre primera_comanda dies
2 Carlos Ferrer Ibáñez 2025-01-22 2025-03-12 49
1 Lucía Martínez Soler 2025-01-10 2025-03-04 53
3 Marta Sanchis Gil 2025-02-03 2025-04-02 58
4 Javier Ortega Ruiz 2025-02-14 2025-04-19 64
5 Ana Belmonte Roca 2025-02-27 2025-05-23 85
6 Pau Llorens Vidal 2025-03-09 2025-06-11 94
7 Sofia Moreira Costa 2025-03-21 2025-06-28 99
8 Tiago Almeida Nunes 2025-04-04 2025-07-15 102

La lectura de negoci és incòmoda: la conversió empitjora a mesura que avança l'any, de 49 a 102 dies. I hi ha un biaix que cal declarar: els clients recents han tingut menys temps per comprar. Amb JOIN normal desapareixen a més els clients 13, 14 i 15, que no han demanat mai res; amb LEFT JOIN apareixerien amb NULL a les dues últimes columnes, que és la resposta honesta.

Solució 3

1. EXTRACT(MONTH …) retorna 3 tant per al març del 2025 com per al del 2026, així que mesura estacionalitat, no evolució: agrupa tots els marços de la història. Amb les dades de BotigaVerda la conclusió és fràgil perquè només hi ha un març amb dades i dotze mesos d'història, de manera que cada "mes de l'any" té entre una i dues comandes. Amb 20 comandes en 12 mesos no hi ha estacionalitat per mesurar: hi ha soroll.

2. La sèrie temporal és la consulta de la secció 5, i la seva lectura és la contrària: mesos bons i dolents alterns, màxim a l'octubre del 2025 (97,20 €) i mínim al novembre (31,70 €), sense tendència clara.

3. Seria correcta si la pregunta fos de debò estacional —"en quin mes de l'any venem més, fent la mitjana de diversos anys?"— i hi hagués prou anys per fer la mitjana. L'incorrecte no és la funció: és fer-la servir per respondre una pregunta d'evolució.

Conclusió

Ja saps treballar amb el temps:

  • Coneixes els cinc tipus temporals i per què BotigaVerda fa servir DATE, amb la recomanació ferma que en una aplicació real TIMESTAMPTZ és gairebé sempre l'elecció correcta. I distingeixes NOW() —congelada durant tota la transacció— de CLOCK_TIMESTAMP().
  • Fas aritmètica de dates sabent que date - date dona dies enters, que date + INTERVAL dona TIMESTAMP i que INTERVAL '1 month' no són 30 dies. I fas servir AGE() quan el destinatari és una persona.
  • Treus components amb EXTRACT (DOW comença en diumenge, WEEK és ISO) i trunques amb DATE_TRUNC, que converteix 20 comandes en una sèrie mensual — el deute de 04-05, saldat. I saps quan fer servir cadascun: EXTRACT per a estacionalitat, DATE_TRUNC per a evolució.
  • Formates amb TO_CHAR (inclòs TMMonth amb lc_time) i analitzes amb TO_DATE, recordant que '03/04/2025' són dues dates diferents i que ordenar text no és ordenar dates.
  • Construeixes informes sense buits amb generate_series + LEFT JOIN + COUNT(columna), i has vist el que amagaven: cinc mesos seguits sense ni una sola alta de client.

Queda un cap solt que has vist tres vegades en aquesta lliçó i no has pogut lligar: a l'informe d'altes per mes va caldre COUNT(c.id) en lloc de COUNT(*) perquè sortís 0; al de facturació mensual va caldre un COALESCE perquè un mes buit no mostrés NULL; i al llistat de clients sense comandes, les columnes de data apareixerien buides sense dir per què. Tots tres són el mateix problema —què fer quan no hi ha valor— i la lliçó següent, conversió de tipus i tractament dels NULL, el resol definitivament amb CAST, COALESCE i NULLIF. A més tancarà un altre compte pendent: has estat escrivint ::DATE, ::NUMERIC i ::TEXT durant tres lliçons sense que ningú t'expliqués què són.

◄ Anterior
◄
Següent ►
►

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats