There is a manual page on Time and Date functions.
The best way to compare date and time and get minutes back is probably to use one of the following:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)
Note that while TIMEDIFF
works with times, TIMESTAMPDIFF
requires a date component - and the values are switched around for TIMESTAMPDIFF
.
You can return hours with either of these:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)
Note that when using DATEDIFF
, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).
To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:
SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)
This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.
If your column definition does not have default constraint and it is nullable, the nullable field will have null
mark, when you add a row even when you specify DEFAULT
keyword for your insert value. For example for the following table definition
CREATE TABLE Test(
ColA [nvarchar](50) NOT NULL,
COLB [nvarchar](50) NULL)
The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.
insert into Test(ColA, ColB) values ('a', DEFAULT);
insert into Test(ColA, ColB) values ('b', NULL);
insert into Test(ColA) values ('c');
insert into Test(ColA, ColB) values ('d', 'e');
Whereas for the following table definition with DEFAULT
constraint.
CREATE TABLE Test(
ColA [nvarchar](50) NOT NULL,
COLB [nvarchar](50) NULL DEFAULT 'x')
Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.
insert into Test(ColA, ColB) values ('a', DEFAULT);
insert into Test(ColA, ColB) values ('b', NULL);
insert into Test(ColA) values ('c');
insert into Test(ColA, ColB) values ('d', 'e');
Best Answer
You could format it with
UNIX_TIMESTAMP
like below:You can find any others date and time functions here. You could add a
trigger
and if you want a default value for yourdecimal(16, 6)
microtime, useBEFORE INSERT
and replace yourNEW.bigintvalue=UNIX_TIMESTAMP(NOW(6));
.Example: