milliseconds in mysql equivalent to java for Partitioning
Hi,
We need to partition few of the tables growing larger. We have designed identifier (Through application)to have the 64 bit primary key with milliseconds(41 bits), 1 bit for signed and 22 bits sequence value to support partitioning. we would like to partition based on the primary key RANGE mainly to archive old data.
Here is our one of the ddl.
CREATE TABLE OB_ORDER
(
CREATED_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CREATED_BY INT UNSIGNED NOT NULL,
LAST_MODIFIED_DATE TIMESTAMP NOT NULL,
LAST_MODIFIED_BY INT UNSIGNED NOT NULL,
ID BIGINT UNSIGNED NOT NULL ,
NAME VARCHAR(512),
ACC_ID INT UNSIGNED,
ORDER_NUMBER VARCHAR(32),
ORDER_DATE TIMESTAMP NULL DEFAULT NULL,
FULLFILL_DATE TIMESTAMP NULL DEFAULT NULL,
STATE INT,
PARTY_ROLE_ID INT UNSIGNED,
PRICE_EFFECTIVE_DATE TIMESTAMP NULL DEFAULT NULL,
TERM DECIMAL(38,10),
CONTRACT_ID INT UNSIGNED,
CONTRACT_VERSION VARCHAR(32),
ACC_CONTACT_ID INT UNSIGNED,
CONSTRAINT ORDER_PK
PRIMARY KEY(ID),
CONSTRAINT OB_ORDER_FK
FOREIGN KEY (CONTRACT_ID)
REFERENCES OB_CONTRACT(ID),
CONSTRAINT ORDER_NUMBER_UC
UNIQUE(ORDER_NUMBER)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;
Please provide me the function to get milliseconds equivaalent to what we get in java using (Calendar lCDateTime = Calendar.getInstance();
date = lCDateTime.getTimeInMillis();)
I tried with SELECT UNIX_TIMESTAMP(); but when cross verified it with FROM_UNIXTIME function it was showing me only date and time and not milliseconds.
Thanks well in advance.