A Little Noise

DATE_TRUNC for MySQL

Written by

snoyes

in

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

Comments

3 responses to “DATE_TRUNC for MySQL”

  1. DATE_TRUNC for MySQL | Dinesh Ram Kali.

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

  2. Ken Lyle Avatar
    Ken Lyle

    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?

  3. Ken Lyle Avatar
    Ken Lyle

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts