trunctime

Truncates a time or timestamp value based on a specified interval.

Syntax

trunctime (dt_expr, tt_spec)

Arguments

dt_expr	A time or timestamp column or value
tt_spec	A string constant containing the interval specification

Description

This function can be called from an SQL SELECT statement to return a time or timestamp value that has been truncated based on an interval specified by the tt_spec argument which must be one of the values given in the following table.

Truncate_time Interval Specification Strings

Interval Spec	Description
"1S"	1 second
"5S"	5 second
"10S"	10 second
"15S"	15 seconds
"20S"	20 second
"30S"	30 seconds
"1M"	1 minute
"5M"	5 minutes
"10M"	10 minutes
"15M"	15 minutes
"20M"	20 minutes
"30M"	30 minutes
"1H"	1 hour
"3H"	3 hours
"4H"	4 hours
"6H"	6 hours
"8H"	8 hours
"12H"	12 hours
"1S"	1 second
"15S"	15 seconds

Example

select truncate_time(time "23:13:15.0123", "5M") interval;
interval
23:10:00

select trunctime(timestamp "2013-10-22 17:23:07", "30M") interval;
interval
2013-10-22 17:00:00.0000

select trunctime(stat_time, "15S") interval, avg(power_output) 
 	from wtstatus group by 1;
interval                        avg(power_output)
2013-10-22 11:05:30.0000                  2795.75
2013-10-22 11:05:45.0000                  2893.45
2013-10-22 11:06:00.0000                  3161.49
2013-10-22 11:06:15.0000                  2902.62
2013-10-22 11:06:30.0000                  2910.44
2013-10-22 11:06:45.0000                  2862.08
2013-10-22 11:07:00.0000                  2771.73
2013-10-22 11:07:15.0000                  3072.36
2013-10-22 11:07:30.0000                  2845.01
2013-10-22 11:07:45.0000                  2979.17
2013-10-22 11:08:00.0000                     3040
2013-10-22 11:08:15.0000                     4150
2013-10-22 11:08:30.0000                     3600
2013-10-22 11:08:45.0000                  4028.57
2013-10-22 11:09:00.0000                  4028.57
2013-10-22 11:09:15.0000                     3275
2013-10-22 11:09:30.0000                     4300