Menu
Is there a postgresql function that will return a timestamp rounded to the nearest minute? The input value is a timestamp and the return value should be a timestamp.
212
In PostgreSQL, it's possible to truncate, or round off, a timestamp value to a specific level of precision. For example, you may want to truncate your date and time value to the nearest hour or even the nearest week. The DATE_TRUNC() function makes it possible to handle this truncation.
Postgres DATE data type Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision. Syntax: date_trunc('datepart', field) The datepart argument in the above syntax is used to truncate one of the field,below listed field type: millennium. century.
Use the built-in function date_trunc(text, timestamp), for example:
select date_trunc('minute', now()) Edit: This truncates to the most recent minute. To get a rounded result, add 30 seconds to the timestamp first, for example:
select date_trunc('minute', now() + interval '30 second') This returns the nearest minute.
See Postgres Date/Time Functions and Operators for more info
93
Answer to a similar (and more generic) question,
"... to the nearest minute interval" (1-minute, 5-minutes, 10-minutes, etc.)
CREATE FUNCTION round_minutes(TIMESTAMP WITHOUT TIME ZONE, integer) RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$ SELECT date_trunc('hour', $1) + cast(($2::varchar||' min') as interval) * round( (date_part('minute',$1)::float + date_part('second',$1)/ 60.)::float / $2::float ) $$ LANGUAGE SQL IMMUTABLE; CREATE FUNCTION round_minutes(TIMESTAMP WITHOUT TIME ZONE, integer,text) RETURNS text AS $$ SELECT to_char(round_minutes($1,$2),$3) $$ LANGUAGE SQL IMMUTABLE; SELECT round_minutes('2010-09-17 16:23:12', 5); -- 2010-09-17 16:25:00 SELECT round_minutes('2010-09-17 16:23:12', 10, 'HH24:MI'); -- 16:20 Adapted from http://wiki.postgresql.org/wiki/Round_time and to the "exact round" as @CrowMagnumb showed.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
