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.
Here are a couple of approaches:
I've simplified your query, as you shouldn't need the TRUNC()
, nor the CAST()
.
SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC';
SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second';
For reference, more information can be found at the following links:
Best Answer
You write "I don't need the time zone information", however datatype
TIMESTAMP WITH LOCAL TIME ZONE
has time zone information stored.For a
TIMESTAMP WITH LOCAL TIME ZONE
column I don't see any difference betweenCURRENT_TIMESTAMP
andLOCALTIMESTAMP
either of them should work in the same way.CURRENT_TIMESTAMP
returns current data and time as of datatypeTIMESTAMP WITH TIME ZONE
, whereasLOCALTIMESTAMP
returns aLOCALTIMESTAMP
value. Both of them have to be implicitly converted to aTIMESTAMP WITH LOCAL TIME ZONE
datatype.