Thread: get date in binary number format
Howdy:
Silly question time -
Is there a way to get the date and change
it into a binary number?
To explain:
One of my colleague suggested that we could start
doing our backups / dump of the database via the
'Tower of Hanoi' algorithm.
The algorithm to select backup number would be the
smallest non-zero place in binary representation of
the backup number (tape).
My thinking is that I could try to figure out a case
statement to get the backup date and from there get
a binary number (an anchor date from the beginning
of the year) unique to that backup dataset (or, tape).
Any suggestions?
Thanks!
-X
Johnson, Shaunn dijo:
> Howdy:
>
> Silly question time -
>
> Is there a way to get the date and change
> it into a binary number?
Well, you can get the day of the year in binary:
testing=> select "bit"((date_part('doy', now()))::integer);
bit
----------------------------------
00000000000000000000000100001101
(1 row)
> The algorithm to select backup number would be the
> smallest non-zero place in binary representation of
> the backup number (tape).
Recursive definition? Sorry, I don't understand the algorithm.
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Ni aun el genio muy grande llegaria muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)
--thanks for the reply:
--this is how the algorithm was explained to me (except
--when i tried the select date part of the sql below, i
--got an error (ERROR: Bad date external representation '1'):
[snip]
Algorithm to select backup number:
smallest non-zero place in binary representation of the backup number
(which in the example is complicated somewhat by the fact we back up
only on weekdays).
e.g.
if the first backup is 2002-01-01 (tuesday) the backup for 8/21/02 would be
(('2002-08-21'::date-2002-01-01'::date-extract('dow' from
'2001-01-01'::datetime))/7)::int4*5
+(extract('dow' from '2002-08-21'::datetime)::int4%6)
which gives us backup #168
168 as binary is 10101000. The "smallest nonzero digit" is the "1" in
the 8's place. numbering the backups as:
8 7 6 5 4 3 2 1 <- backup set ("tape") #
1 0 1 0 1 0 0 0 <- binary of backup #
using "9-length(rtrim( --previous-result-- , '0'))", we would use backup
set ("tape") # 4
we will always have 1, 2 and no more than 4-day old backups.
going to a depth of 8 "sets" us a max history of between 25 and 50
weeks. 10 "sets" goes back 2 to 4 years, and no tape drive will need to
be purchased, or maintained, and no tapes can be lost.
[/snip]
--not sure if anyone else has heard of this
--but it was a new one to me ... hope that at least
--explains some of what i'm trying to do.
--thanks again!
-X
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@atentus.com]
Sent: Thursday, September 26, 2002 11:50 AM
To: Johnson, Shaunn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] get date in binary number format
Johnson, Shaunn dijo:
> Howdy:
>
> Silly question time -
>
> Is there a way to get the date and change
> it into a binary number?
Well, you can get the day of the year in binary:
testing=> select "bit"((date_part('doy', now()))::integer);
bit
----------------------------------
00000000000000000000000100001101
(1 row)
> The algorithm to select backup number would be the
> smallest non-zero place in binary representation of
> the backup number (tape).
Recursive definition? Sorry, I don't understand the algorithm.
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Ni aun el genio muy grande llegaria muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)
Howdy:
I get this when I try the following select:
[snip]
testdb=> select "bit"((date_part('doy', now()))::integer);
ERROR: Function 'bit(int4)' does not exist
Unable to identify a function that satisfies the given
argument types
You may need to add explicit typecasts
[/snip]
Is there something that I should know about "bit"?
Is it a pre-defined function (system) or is this
something I don't have in my version of PostgreSQL?
Thanks all!
-X
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@atentus.com]
Sent: Thursday, September 26, 2002 11:50 AM
To: Johnson, Shaunn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] get date in binary number format
Johnson, Shaunn dijo:
> Howdy:
>
> Silly question time -
>
> Is there a way to get the date and change
> it into a binary number?
Well, you can get the day of the year in binary:
testing=> select "bit"((date_part('doy', now()))::integer);
bit
----------------------------------
00000000000000000000000100001101
(1 row)
> The algorithm to select backup number would be the
> smallest non-zero place in binary representation of
> the backup number (tape).
Recursive definition? Sorry, I don't understand the algorithm.
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Ni aun el genio muy grande llegaria muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)
The function bit is as you say a predefined function in Postgres
It shows as follows
bit bit(bit,integer)
Taking in bit and integer and returning bit
HTH
On Thu, 26 Sep 2002, Johnson, Shaunn wrote:
> Howdy:
>
> I get this when I try the following select:
>
> [snip]
>
> testdb=> select "bit"((date_part('doy', now()))::integer);
> ERROR: Function 'bit(int4)' does not exist
> Unable to identify a function that satisfies the given
> argument types
> You may need to add explicit typecasts
>
> [/snip]
>
> Is there something that I should know about "bit"?
> Is it a pre-defined function (system) or is this
> something I don't have in my version of PostgreSQL?
>
> Thanks all!
>
> -X
>
>
> -----Original Message-----
> From: Alvaro Herrera [mailto:alvherre@atentus.com]
> Sent: Thursday, September 26, 2002 11:50 AM
> To: Johnson, Shaunn
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] get date in binary number format
>
>
> Johnson, Shaunn dijo:
>
> > Howdy:
> >
> > Silly question time -
> >
> > Is there a way to get the date and change
> > it into a binary number?
>
> Well, you can get the day of the year in binary:
>
> testing=> select "bit"((date_part('doy', now()))::integer);
> bit
> ----------------------------------
> 00000000000000000000000100001101
> (1 row)
>
>
> > The algorithm to select backup number would be the
> > smallest non-zero place in binary representation of
> > the backup number (tape).
>
> Recursive definition? Sorry, I don't understand the algorithm.
>
>
--
Darren Ferguson
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> I get this when I try the following select:
> testdb=> select "bit"((date_part('doy', now()))::integer);
> ERROR: Function 'bit(int4)' does not exist
In CVS tip I get
regression=# select "bit"((date_part('doy', now()))::integer);
bit
----------------------------------
00000000000000000000000100001101
(1 row)
but I think the int4-to-bit(32) conversion function was added for 7.3.
There also seems to be an int8-to-bit(64) function now:
regression=# select "bit"((date_part('doy', now()))::int8);
bit
------------------------------------------------------------------
0000000000000000000000000000000000000000000000000000000100001101
(1 row)
IIRC, Tom Lockhart put in both of those a couple months ago.
regards, tom lane
Tom Lane dijo:
> "Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> > I get this when I try the following select:
>
> > testdb=> select "bit"((date_part('doy', now()))::integer);
> > ERROR: Function 'bit(int4)' does not exist
>
> In CVS tip I get
>
> regression=# select "bit"((date_part('doy', now()))::integer);
> bit
> ----------------------------------
> 00000000000000000000000100001101
> (1 row)
Is there a way to cast these bitstrings into varchars or something? I
can't find any.
--
Alvaro Herrera (<alvherre[a]atentus.com>)
Hi! I'm a .signature virus!
cp me into your .signature file to help me spread!
Alvaro Herrera <alvherre@atentus.com> writes:
> Is there a way to cast these bitstrings into varchars or something? I
> can't find any.
regression=# select ("bit"((date_part('doy', now()))::integer))::text;
ERROR: Cannot cast type bit to text
Evidently not :-(.
Now that we have the ability to create casts that aren't applied
implicitly, it should be safe to create a lot more casts than we have
now, for example the bool<->int conversions that people keep asking for.
We ran out of time to attack that in 7.3, but I hope we will do more
in 7.4.
regards, tom lane
Tom Lane dijo:
> Alvaro Herrera <alvherre@atentus.com> writes:
> > Is there a way to cast these bitstrings into varchars or something? I
> > can't find any.
>
> regression=# select ("bit"((date_part('doy', now()))::integer))::text;
> ERROR: Cannot cast type bit to text
Well, there is:
testing=> select varcharin(bit_out(bitv), 0, length(bitv)+4 ) FROM
(select "bit"(1) AS bitv) as t;
varcharin
----------------------------------
00000000000000000000000000000001
(1 row)
So going back to Shaunn's question, it's rather confusing to set
everything up (you'll have to use cut'n pasting from this message and my
previous followup), BUT it doesn't work in previous releases (only in
7.3beta). Maybe you can adapt it, but it's not clear to me that the
casting from bit to varchar is possible in 7.2.
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La realidad se compone de muchos sueños, todos ellos diferentes,
pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)
Alvaro Herrera <alvherre@atentus.com> writes:
> Tom Lane dijo:
>> regression=# select ("bit"((date_part('doy', now()))::integer))::text;
>> ERROR: Cannot cast type bit to text
> Well, there is:
> testing=> select varcharin(bit_out(bitv), 0, length(bitv)+4 ) FROM
> (select "bit"(1) AS bitv) as t;
Oh, of course, I keep forgetting that now that cstring is a first-class
type, all things are possible via abutted I/O function calls ;-)
You could simplify that as
select varcharin(bit_out("bit"(1)), 0, -1);
But either way you must admit that the above is an ugly hack not proper
support.
regards, tom lane