The second form of your query will work if you make your mark your function as deterministic, meaning that for a given set of input values, it will always return the same result.
With that set, Oracle will only run the conversion once for each parameters in the where
clause rather than for every row.
With this:
CREATE OR REPLACE FUNCTION TO_MINUTE_D (DATE_IN IN DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
/* Minute 0 = 12/30/1899 12:00am */
RETURN
(TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
TO_NUMBER(TO_CHAR(DATE_IN, 'HH24')) * 60 +
TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE_D;
/
On a table filled with a large bunch of dummy rows (increasing ints), I get the following timings consistently:
SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));
no rows selected
Elapsed: 00:00:12.69
versus deterministic-annotated function:
SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE_D(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE_D(TO_DATE('2013-01-01', 'YYYY-MM-DD'));
no rows selected
Elapsed: 00:00:00.07
You should get very close to what you have with the values plugged in directly, and indexes on those columns can be used as if you'd plugged in literals.
(Putting the conversion function on the start|stop_minute
columns isn't a good idea in general as you've discovered, unless you have a function-based index on those that matches exactly.)
You could format it with UNIX_TIMESTAMP
like below:
mysql> SELECT UNIX_TIMESTAMP(NOW(6));
+------------------------+
| UNIX_TIMESTAMP(NOW(6)) |
+------------------------+
| 1442068528.543100 |
+------------------------+
1 row in set (0,00 sec)
mysql>
You can find any others date and time functions here. You could add a trigger
and if you want a default value for your decimal(16, 6)
microtime, use BEFORE INSERT
and replace your NEW.bigintvalue=UNIX_TIMESTAMP(NOW(6));
.
Example:
mysql> CREATE TABLE `test`.`test1` (
-> `id` INT NOT NULL AUTO_INCREMENT,
-> `time` DECIMAL(16, 6) NULL,
-> PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`test1_BEFORE_INSERT` BEFORE INSERT ON `test1` FOR EACH ROW
-> BEGIN
-> SET NEW.time=UNIX_TIMESTAMP(NOW(6));
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> # Adding 1 into id field
mysql> INSERT INTO `test`.`test1` (`id`) VALUES ('1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.test1;
+----+-------------------+
| id | time |
+----+-------------------+
| 1 | 1442069359.675330 |
+----+-------------------+
1 row in set (0.00 sec)
mysql>
Best Answer