Command guide

advanced date-time MySQL MariaDB SQL Server PostgreSQL SQLite

Round a Timestamp Down to a Specific Unit

Truncate a timestamp to the start of a month, day, or hour using engine-specific functions.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Truncate event timestamps to the first day of each month

Both March events truncate to 2024-03-01; the April event truncates to 2024-04-01. PostgreSQL's DATE_TRUNC('month', ts) is the most direct form. SQL Server's arithmetic computes the number of whole months since epoch 0 and then adds them back. MySQL and MariaDB hard-code day 01 in the format string, which achieves the same result.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_time VARCHAR(30));

INSERT INTO
  events (id, name, event_time)
VALUES
  (1, 'signup', '2024-03-15 09:30:00'),
  (2, 'purchase', '2024-03-22 14:00:00'),
  (3, 'refund', '2024-04-03 11:45:00');

Validated query

SQL

SELECT
  id,
  name,
  DATE_FORMAT (event_time, '%Y-%m-01') AS month_start
FROM
  events
ORDER BY
  id;

Expected result

id	name	month_start
1	signup	2024-03-01
2	purchase	2024-03-01
3	refund	2024-04-01

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_time VARCHAR(30));

INSERT INTO
  events (id, name, event_time)
VALUES
  (1, 'signup', '2024-03-15 09:30:00'),
  (2, 'purchase', '2024-03-22 14:00:00'),
  (3, 'refund', '2024-04-03 11:45:00');

Validated query

SQL

SELECT
  id,
  name,
  CONVERT(
    VARCHAR(10),
    DATEADD (
      MONTH,
      DATEDIFF (MONTH, 0, CAST(event_time AS DATETIME)),
      0
    ),
    23
  ) AS month_start
FROM
  events
ORDER BY
  id;

Expected result

id	name	month_start
1	signup	2024-03-01
2	purchase	2024-03-01
3	refund	2024-04-01

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_time VARCHAR(30));

INSERT INTO
  events (id, name, event_time)
VALUES
  (1, 'signup', '2024-03-15 09:30:00'),
  (2, 'purchase', '2024-03-22 14:00:00'),
  (3, 'refund', '2024-04-03 11:45:00');

Validated query

SQL

SELECT id, name, TO_CHAR(DATE_TRUNC('month', event_time::TIMESTAMP), 'YYYY-MM-DD') AS month_start FROM events ORDER BY id;

Expected result

id	name	month_start
1	signup	2024-03-01
2	purchase	2024-03-01
3	refund	2024-04-01

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_time VARCHAR(30));

INSERT INTO
  events (id, name, event_time)
VALUES
  (1, 'signup', '2024-03-15 09:30:00'),
  (2, 'purchase', '2024-03-22 14:00:00'),
  (3, 'refund', '2024-04-03 11:45:00');

Validated query

SQL

SELECT
  id,
  name,
  strftime ('%Y-%m-01', event_time) AS month_start
FROM
  events
ORDER BY
  id;

Expected result

id	name	month_start
1	signup	2024-03-01
2	purchase	2024-03-01
3	refund	2024-04-01

MySQL and MariaDB use DATE_FORMAT; SQL Server uses DATEADD/DATEDIFF; PostgreSQL uses DATE_TRUNC. Results are identical.

Useful when

Where this command helps.

grouping events by month or day for time-series aggregation
normalizing timestamps to a common boundary before joining or comparing

Explanation

What the command is doing.

Truncating a timestamp strips the sub-unit portions and returns the start of the chosen unit. PostgreSQL has a native DATE_TRUNC function. SQL Server 2022+ adds DATETRUNC; older versions use DATEADD/DATEDIFF arithmetic. MySQL and MariaDB use DATE_FORMAT to format the date with zeroed sub-components. All approaches return the first moment of the target unit.

Round a Timestamp Down to a Specific Unit

Truncate event timestamps to the first day of each month

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.