Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to truncate? integers

How to truncate? integers

From
Johann Schatzer
Date:
How can I ~truncate~ integers?


this column

4770
4780
4800
4810
4840
4900
4930
4950
4960
5070
5090
5130

should give

4700
4700
4800
4800
4800
4900
4900
4900
4900
5000
5000
5100

thank you

Re: How to truncate? integers

From
Richard Broersma
Date:
On Wed, Nov 16, 2011 at 12:26 PM, Johann Schatzer
<schatzer.johann@gmail.com> wrote:
> How can I ~truncate~ integers?
> this column
> 4770
> should give
> 4700

postgres=# SELECT (4770::int)/100*100;
 ?column?
----------
     4700
(1 row)



--
Regards,
Richard Broersma Jr.

Re: How to truncate? integers

From
Leif Biberg Kristensen
Date:
On Wednesday 16. November 2011 21.26.19 Johann Schatzer wrote:
> How can I ~truncate~ integers?
>
>
> this column
>
> 4770
> 4780
> 4800
> 4810
> 4840
> 4900
> 4930
> 4950
> 4960
> 5070
> 5090
> 5130
>
> should give
>
> 4700
> 4700
> 4800
> 4800
> 4800
> 4900
> 4900
> 4900
> 4900
> 5000
> 5000
> 5100
>
> thank you

Divide by 100, keep integer part, and multiply again by 100:

test=> select (4770/100)::integer * 100;
 ?column?
----------
     4700
(1 row)

Time: 0,426 ms
test=>

regards,
Leif

Re: How to truncate? integers

From
"Jean-Yves F. Barbier"
Date:
On Wed, 16 Nov 2011 21:26:19 +0100
Johann Schatzer <schatzer.johann@gmail.com> wrote:

SELECT trunc(4770, -2);
 trunc
-------
  4700
(1 ligne)

> How can I ~truncate~ integers?
>
>
> this column
>
> 4770
> should give
>
> 4700

--
Q:    How do you tell if an Elephant has been making love in your backyard?
A:    If all your trashcan liners are missing ...

Re: How to truncate? integers

From
Steve Crawford
Date:
On 11/16/2011 12:26 PM, Johann Schatzer wrote:
> How can I ~truncate~ integers?
>
>
> this column
>
> 4770
> ...
>
> should give
>
> 4700
> ...

To the next lower 100? Try this:

select floor(4770*.01)*100;
  ?column?
----------
      4700

Cheers,
Steve

Re: How to truncate? integers

From
Steve Crawford
Date:
On 11/16/2011 12:26 PM, Johann Schatzer wrote:
> How can I ~truncate~ integers?
>
>
> this column
>
> 4770
> ...
>
> should give
>
> 4700
> ...
>
> thank you

Oh, you can also use the behavior of integer math:

select (4770/100)*100;
  ?column?
----------
      4700

If you have negative numbers, you need to determine how they should be
truncated. The two methods will behave differently:

select (-4770/100)*100;
  ?column?
----------
     -4700


select floor(-4770*.01)*100;
  ?column?
----------
     -4800

Cheers,
Steve