A Little Noise

October 7, 2015

DATE_TRUNC for MySQL

Filed under: MySQL — snoyes @ 12:52 pm

Because somebody asked for it on Freenode:

CREATE FUNCTION DATE_TRUNC(field ENUM('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', 'millennium'), source datetime(6))
RETURNS datetime(6)
DETERMINISTIC
BEGIN
  IF field IN ('millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) % 1000 MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second') THEN SET source = source - INTERVAL SECOND(source) SECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute') THEN SET source = source - INTERVAL MINUTE(source) MINUTE; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour') THEN SET source = source - INTERVAL HOUR(source) HOUR; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day') THEN SET source = source - INTERVAL DAYOFWEEK(source) - 1 DAY; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week') THEN SET source = source - INTERVAL DAY(source) - 1 DAY; END IF;
  IF field IN ('quarter') THEN SET source = source - INTERVAL MONTH(source) % 3 - 1 MONTH; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'week', 'day', 'month', 'quarter') THEN SET source = source - INTERVAL MONTH(source) - 1 MONTH; END IF;

  -- Year ranges go from 1 - 10, e.g. 1961-1970, not 1960-1969. The third millenium started 2001, not 2000. If you want it the other way, remove the "- 1" from each of the following.
  IF field IN ('decade') THEN SET source = source - INTERVAL YEAR(source) % 10 - 1 YEAR; END IF;
  IF field IN ('century') THEN SET source = source - INTERVAL YEAR(source) % 100  - 1 YEAR; END IF;
  IF field IN ('millennium') THEN SET source = source - INTERVAL YEAR(source) % 1000 - 1 YEAR; END IF;
 
  RETURN source;
END

When called with the date ‘1996-02-29 12:28:53.123456’, returns the following:

FIELD Returned value
MICROSECOND 1996-02-29 12:28:53.123456
MILLISECOND 1996-02-29 12:28:53.123000
SECOND 1996-02-29 12:28:53.000000
MINUTE 1996-02-29 12:28:00.000000
HOUR 1996-02-29 12:00:00.000000
DAY 1996-02-29 00:00:00.000000
WEEK 1996-02-25 00:00:00.000000
MONTH 1996-02-01 00:00:00.000000
QUARTER 1996-01-01 00:00:00.000000
YEAR 1996-01-01 00:00:00.000000
DECADE 1991-01-01 00:00:00.000000
CENTURY 1901-01-01 00:00:00.000000
MILLENNIUM 1001-01-01 00:00:00.000000

3 Comments »

  1. […] DATE_TRUNC for MySQL // Planet MySQL […]

    Pingback by DATE_TRUNC for MySQL | Dinesh Ram Kali. — October 8, 2015 @ 3:15 am

  2. Is the idea that there is a datetime field called source, and one uses update statements, perhaps as part of an insert trigger, to fill the fields listed here?

    Comment by Ken Lyle — October 12, 2018 @ 12:31 pm

  3. To execute this Create Function in PHPMyAdmin, wrap it in

    DELIMITER $$
    .
    .
    .
    $$
    DELIMITER ;

    and enjoy

    If you are batch updating your database you might like to create the fields:

    ====Begin Date_Trunc function database adjustments…

    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `microsecond` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `millisecond` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `second` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `minute` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `hour` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `day` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `week` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `month` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `quarter` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `year` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `decade` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `century` datetime;
    Alter table `REPLACE_ME_WITH_YOUR_TABLENAME` add column `millennium` datetime
    ====End Date_Trunc function database adjustments…

    And to run the updates:

    Update `REPLACE_ME_WITH_YOUR_TABLENAME` set `hour` = DATE_TRUNC(‘hour’,`DDateTime`);
    Update `REPLACE_ME_WITH_YOUR_TABLENAME` set `day` = DATE_TRUNC(‘day’,`DDateTime`);
    Update `REPLACE_ME_WITH_YOUR_TABLENAME` set `week` = DATE_TRUNC(‘week’,`DDateTime`);
    Update `REPLACE_ME_WITH_YOUR_TABLENAME` set `month` = DATE_TRUNC(‘month’,`DDateTime`);
    Update `REPLACE_ME_WITH_YOUR_TABLENAME` set `quarter` = DATE_TRUNC(‘quarter’,`DDateTime`);
    Update `REPLACE_ME_WITH_YOUR_TABLENAME` set `year` = DATE_TRUNC(‘year’,`DDateTime`)

    Where DDateTime is the field with the source …um, yeah, you guessed it…Datetime.

    Comment by Ken Lyle — October 12, 2018 @ 1:23 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress