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 |
[…] DATE_TRUNC for MySQL // Planet MySQL […]
Pingback by DATE_TRUNC for MySQL | Dinesh Ram Kali. — October 8, 2015 @ 3:15 am
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
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