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.
I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows
You don't need extract
in order to get the data:
select *
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';
will happily use an index on moment_in_time
and will be very efficient - in fact there will be no difference in performance compared to
select *
from your_table
where milliseconds_column >= 1187906400000;
Now look at the two statements and tell me for which one you immediately understand what the query will return.
If you do need those milliseconds in your application then you can always do something like this:
select some_col,
other_col,
extract(epoch from timestamp_column) * 1000 as millis
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';
And again, I'm almost 100% certain that you won't see any performance difference to:
select some_col,
other_col,
milliseconds_column
from your_table
where milliseconds_column >= 1187906400000;
Plus: if you do store the milliseconds and you want to display the real date, you always need to apply an additional conversion:
select some_col,
other_col,
to_char(timestamp_column, 'yyyy-mm-dd') as the_date
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';
vs.
select some_col,
other_col,
to_char(to_timestamp(milliseconds_column / 1000), 'yyyy-mm-dd) as the_date
from your_table
where milliseconds_column >= 1187906400000;
The second one one be slower than the first one, but it's more typing and makes the code harder to read.
Edit:
how to create a stored procedure that will convert this timestamp into the milliseconds I want (and the reverse function too)?
You don't need to write your own function:
- convert timestamp to milliseconds:
extract(epoch from current_timestamp) * 1000 + extract(milliseconds from current_timestamp)
- convert milliseconds to timestamp:
to_timestamp(1187980443530 / 1000)
So to wrap it up:
Go with a timestamp
column and don't worry about performance.
Best Answer
time with time zone
stores microseconds (8 bytes) and the time zone (4 bytes).timestamp with time zone
stores just the microseconds and converts the time zone at display time. Because of the conceptual weirdness of thetime with time zone
type, the time zone needs to be stored explicitly. You don't actually need 8 bytes to store the number of microseconds in a day, but 4 bytes wouldn't be enough. If you really wanted to, you could probably devise a more compact storage format fortime with time zone
, but in practice nobody cares.