Styjun

Multi tool use

Find the 3D region containing the origin bounded by given planes

Can't think of a good word or term to describe not feeling or thinking

Why didn't Daenerys' advisers suggest assassinating Cersei?

Should I twist DC power and ground wires from a power supply?

Is presenting a play showing Military charactes in a bad light a crime in the US?

Why is python script running in background consuming 100 % CPU?

How can I prevent Bash expansion from passing files starting with "-" as argument?

Germany rejected my entry to Schengen countries

If you attack a Tarrasque while swallowed, what AC do you need to beat to hit it?

Why is so much ransomware breakable?

Bash Read: Reading comma separated list, last element is missed

How does the "reverse syntax" in Middle English work?

Bookshelves: the intruder

How to safely discharge oneself

How to fix "webpack Dev Server Invalid Options" in Vuejs

Good examples of "two is easy, three is hard" in computational sciences

Managing heat dissipation in a magic wand

How could the B-29 bomber back up under its own power?

Novel where a cube cooled below absolute zero makes a hole in reality

Windows reverting changes made by Linux to FAT32 partition

How do we explain the use of a software on a math paper?

Bash Array of Word-Splitting Headaches

Is it a good idea to teach algorithm courses using pseudocode instead of a real programming language?

Why should one apply for UK visa before other visas, on a multi-destination European holiday?

postgresql date_trunc to arbitrary precision?

Postgresql: round up the time to nearest 15 minute range (0,15,30,45)PostgreSQL “DESCRIBE TABLE”Show tables in PostgreSQLCreating a copy of a database in PostgreSQLHow can I drop all the tables in a PostgreSQL database?How to start PostgreSQL server on Mac OS X?How to exit from PostgreSQL command line utility: psqldate_trunc 5 minute interval in PostgreSQLHow to change PostgreSQL user password?Which version of PostgreSQL am I running?PostgreSQL dynamic date_trunc function rounding up exactly to given timestamp

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?

I know one trick is to convert the time stamp to epoch, do some math, then convert back. But I don't like it.

postgresql

share|improve this question

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

add a comment | 

0

postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?

I know one trick is to convert the time stamp to epoch, do some math, then convert back. But I don't like it.

postgresql

share|improve this question

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

add a comment | 

postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?

I know one trick is to convert the time stamp to epoch, do some math, then convert back. But I don't like it.

postgresql

share|improve this question

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

share|improve this question

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

share|improve this question

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

asked Sep 8 '14 at 4:52

David S.David S.

5,39174579

2 Answers
2

active

oldest

votes

1

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

share|improve this answer

edited Mar 23 at 18:36

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @alex-dvortsky Thank you very much. Can you help me come up with a solution that I can use n as the round up unit? Say interval 'n min'
  
  – David S.
  Sep 9 '14 at 12:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sure. I've updated my answer for any n
  
  – Alex Dvoretsky
  Sep 12 '14 at 18:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Quick update needed: the prototype of the second function is wrong: CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) would be more accurate (edited the name, added missing second parameter)
  
  – Romain G
  Nov 22 '16 at 16:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks. Fixed signature of the function
  
  – Alex Dvoretsky
  Nov 23 '16 at 13:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the second function seems to not actually work: jasen=# select round_time_nm(now(),10); -[ RECORD 1 ]-+----------------------- round_time_nm | 2019-03-17 23:02:00+00
  
  – Jasen
  Mar 17 at 23:12
  
  

  
  
  
  

 | 
show 1 more comment

0

here's an improved version of date_trunc

create cast (bigint as timestamptz) WITHOUT FUNCTION;
create cast (timestamptz as bigint) WITHOUT FUNCTION;
CREATE OR REPLACE FUNCTION date_trunc_by_interval( interval, timestamptz )
 RETURNS timestamptz 
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT
 AS $$ 
 select
 case when $2::bigint >= 0::bigint then
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 else
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 - (extract (epoch from $1)*1000000 ) ::bigint
 end ::timestamptz
$$;

this allows rounding to any fixed-length interval eg: '864 seconds' (divinding days into 100 parts) or '14 days' dividing the calendar into fortnights. the basis is '2000-01-01 00:00:00.0 +00' which is the epoch used to compute postgres
timestamp values.

it works by coercing the timestamptz value and the interval into bigints and doing integer arithmetic on them then coercing them back to timestamps

negative inputs need special handling (the case statement) as % causes rounding towards zero.

share|improve this answer

answered Mar 17 at 23:17

JasenJasen

8,64111938

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f25717654%2fpostgresql-date-trunc-to-arbitrary-precision%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

share|improve this answer

edited Mar 23 at 18:36

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @alex-dvortsky Thank you very much. Can you help me come up with a solution that I can use n as the round up unit? Say interval 'n min'
  
  – David S.
  Sep 9 '14 at 12:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sure. I've updated my answer for any n
  
  – Alex Dvoretsky
  Sep 12 '14 at 18:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Quick update needed: the prototype of the second function is wrong: CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) would be more accurate (edited the name, added missing second parameter)
  
  – Romain G
  Nov 22 '16 at 16:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks. Fixed signature of the function
  
  – Alex Dvoretsky
  Nov 23 '16 at 13:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the second function seems to not actually work: jasen=# select round_time_nm(now(),10); -[ RECORD 1 ]-+----------------------- round_time_nm | 2019-03-17 23:02:00+00
  
  – Jasen
  Mar 17 at 23:12
  
  

  
  
  
  

 | 
show 1 more comment

1

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

share|improve this answer

edited Mar 23 at 18:36

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @alex-dvortsky Thank you very much. Can you help me come up with a solution that I can use n as the round up unit? Say interval 'n min'
  
  – David S.
  Sep 9 '14 at 12:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sure. I've updated my answer for any n
  
  – Alex Dvoretsky
  Sep 12 '14 at 18:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Quick update needed: the prototype of the second function is wrong: CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) would be more accurate (edited the name, added missing second parameter)
  
  – Romain G
  Nov 22 '16 at 16:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks. Fixed signature of the function
  
  – Alex Dvoretsky
  Nov 23 '16 at 13:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the second function seems to not actually work: jasen=# select round_time_nm(now(),10); -[ RECORD 1 ]-+----------------------- round_time_nm | 2019-03-17 23:02:00+00
  
  – Jasen
  Mar 17 at 23:12
  
  

  
  
  
  

 | 
show 1 more comment

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

share|improve this answer

edited Mar 23 at 18:36

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
 SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

share|improve this answer

edited Mar 23 at 18:36

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

answered Sep 8 '14 at 5:18

Alex DvoretskyAlex Dvoretsky

756518

0

here's an improved version of date_trunc

create cast (bigint as timestamptz) WITHOUT FUNCTION;
create cast (timestamptz as bigint) WITHOUT FUNCTION;
CREATE OR REPLACE FUNCTION date_trunc_by_interval( interval, timestamptz )
 RETURNS timestamptz 
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT
 AS $$ 
 select
 case when $2::bigint >= 0::bigint then
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 else
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 - (extract (epoch from $1)*1000000 ) ::bigint
 end ::timestamptz
$$;

this allows rounding to any fixed-length interval eg: '864 seconds' (divinding days into 100 parts) or '14 days' dividing the calendar into fortnights. the basis is '2000-01-01 00:00:00.0 +00' which is the epoch used to compute postgres
timestamp values.

it works by coercing the timestamptz value and the interval into bigints and doing integer arithmetic on them then coercing them back to timestamps

negative inputs need special handling (the case statement) as % causes rounding towards zero.

share|improve this answer

answered Mar 17 at 23:17

JasenJasen

8,64111938

add a comment | 

0

here's an improved version of date_trunc

create cast (bigint as timestamptz) WITHOUT FUNCTION;
create cast (timestamptz as bigint) WITHOUT FUNCTION;
CREATE OR REPLACE FUNCTION date_trunc_by_interval( interval, timestamptz )
 RETURNS timestamptz 
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT
 AS $$ 
 select
 case when $2::bigint >= 0::bigint then
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 else
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 - (extract (epoch from $1)*1000000 ) ::bigint
 end ::timestamptz
$$;

this allows rounding to any fixed-length interval eg: '864 seconds' (divinding days into 100 parts) or '14 days' dividing the calendar into fortnights. the basis is '2000-01-01 00:00:00.0 +00' which is the epoch used to compute postgres
timestamp values.

it works by coercing the timestamptz value and the interval into bigints and doing integer arithmetic on them then coercing them back to timestamps

negative inputs need special handling (the case statement) as % causes rounding towards zero.

share|improve this answer

answered Mar 17 at 23:17

JasenJasen

8,64111938

add a comment | 

here's an improved version of date_trunc

create cast (bigint as timestamptz) WITHOUT FUNCTION;
create cast (timestamptz as bigint) WITHOUT FUNCTION;
CREATE OR REPLACE FUNCTION date_trunc_by_interval( interval, timestamptz )
 RETURNS timestamptz 
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT
 AS $$ 
 select
 case when $2::bigint >= 0::bigint then
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 else
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 - (extract (epoch from $1)*1000000 ) ::bigint
 end ::timestamptz
$$;

this allows rounding to any fixed-length interval eg: '864 seconds' (divinding days into 100 parts) or '14 days' dividing the calendar into fortnights. the basis is '2000-01-01 00:00:00.0 +00' which is the epoch used to compute postgres
timestamp values.

it works by coercing the timestamptz value and the interval into bigints and doing integer arithmetic on them then coercing them back to timestamps

negative inputs need special handling (the case statement) as % causes rounding towards zero.

share|improve this answer

answered Mar 17 at 23:17

JasenJasen

8,64111938

create cast (bigint as timestamptz) WITHOUT FUNCTION;
create cast (timestamptz as bigint) WITHOUT FUNCTION;
CREATE OR REPLACE FUNCTION date_trunc_by_interval( interval, timestamptz )
 RETURNS timestamptz 
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT
 AS $$ 
 select
 case when $2::bigint >= 0::bigint then
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 else
 $2::bigint - $2::bigint % (extract (epoch from $1)*1000000 ) ::bigint 
 - (extract (epoch from $1)*1000000 ) ::bigint
 end ::timestamptz
$$;

share|improve this answer

answered Mar 17 at 23:17

JasenJasen

8,64111938

answered Mar 17 at 23:17

JasenJasen

8,64111938

answered Mar 17 at 23:17

JasenJasen

8,64111938