milliseconds in mysql equivalent to java for Partitioning
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 ,
ACC_ID INT UNSIGNED,
ORDER_DATE TIMESTAMP NULL DEFAULT NULL,
FULLFILL_DATE TIMESTAMP NULL DEFAULT NULL,
PARTY_ROLE_ID INT UNSIGNED,
PRICE_EFFECTIVE_DATE TIMESTAMP NULL DEFAULT NULL,
CONTRACT_ID INT UNSIGNED,
ACC_CONTACT_ID INT UNSIGNED,
FOREIGN KEY (CONTRACT_ID)
) 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.