My Oracle Support Banner

An Example Of Partitioning By Logical Week Without Using EXTRACT() Function With WEEK Specifier (Doc ID 2861025.1)

Last updated on MAY 02, 2022

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

Goal

 One of the limitations in partitioning is that the function EXTRACT() is not permitted as a partitioning function when it specifies the unit as WEEK (Bug #54483) as mentioned in the online MySQL manual, here:
https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-functions.html

This document shows an alternative way to achieve partitioning by week. It distributes the data into 7-day blocks. By not using the EXTRACT() function with WEEK specifier, it does not depend on the setting of the system variable default_week_format. Hence, a particular value of the date always is assigned to the same partition. Thus the assignment of partition is deterministic.

Requirements

R1. the earliest date in the data is 01-Jan-2021.
R2. the table comprises 90 days of data

Original table description is as follows:

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
 Requirements
Solution
 Test Data:
 Verification:
 value of hired
 stored in partition


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.