Thread: Need timestamp function that will change within a transaction
I'd like to find a timestamp function that WILL change within a transaction. This function will return to me a 15 digit BIGINT number in base10: SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 100000)::bigint; The problem is that NOW() does not change within a transaction and so I keep getting the same value. Is there a function that will return a timestamp that is NOT consistent within a transaction? -- Dante
On May 14, 2008, at 12:56 PM, D. Dante Lorenso wrote: > I'd like to find a timestamp function that WILL change within a > transaction. > > This function will return to me a 15 digit BIGINT number in base10: > > SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 100000)::bigint; > > The problem is that NOW() does not change within a transaction and > so I keep getting the same value. Is there a function that will > return a timestamp that is NOT consistent within a transaction? Take a look at statement_timestamp() or clock_timestamp() Cheers, Steve
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Le 14 mai 08 à 21:56, D. Dante Lorenso a écrit : > I'd like to find a timestamp function that WILL change within a > transaction. See the fine manual: http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT It saith that timeofday() and clock_timestamp() are what you're after. Most likely it's clock_timestamp(). Regards, - -- dim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkgrRr8ACgkQlBXRlnbh1bmrjgCgqjOiIkQ+EvZi262FYYzRjol1 V9QAoIoj9wYb01K8e939F/LkZ2fDjVpg =rI75 -----END PGP SIGNATURE-----
I'd like to convert very large unsigned numbers (ala bigint) to a text
string using base62. I created this PL/PERL function to do the trick:
--------------------
CREATE OR REPLACE FUNCTION "public"."ls_crypt_convert_base" (in_value
text, in_base integer) RETURNS text AS
$body$
my ($value, $base) = @_;
$base = ($base > 62) ? 62 : (($base < 2) ? 2 : $base);
my @nums = (0..9,'a'..'z','A'..'Z')[0..$base-1];
my $index = 0;
my %nums = map {$_, $index++} @nums;
# short circuit if no value
$value =~ s/\D//g;
return if ($value == 0);
# this will be the end value.
my $rep = '';
while ($value > 0) {
$rep = $nums[$value % $base] . $rep;
$value = int($value / $base);
}
return $rep;
$body$
LANGUAGE 'plperl' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
--------------------
# SELECT ls_crypt_convert_base(999999999999999999::text, 62);
ls_crypt_convert_base
-----------------------
1bS0EMtBbK8
(1 row)
# SELECT ls_crypt_convert_base(888888888888888888::text, 62);
ls_crypt_convert_base
-----------------------
13F7tmqjhmu
(1 row)
# SELECT ls_crypt_convert_base(7777777777::text, 62);
ls_crypt_convert_base
-----------------------
8umLiF
(1 row)
# SELECT ls_crypt_convert_base(123456789::text, 62);
ls_crypt_convert_base
-----------------------
8m0Kx
(1 row)
Did I just reinvent the wheel? It seems like something like this is
should already be built into PostgreSQL and I just don't know where to look.
-- Dante
> I'd like to convert very large unsigned numbers (ala bigint) to a text > string using base62. I created this PL/PERL function to do the trick: base 62 is cruel and unusual punishment. Introduce two more printing characters to your set a..z, A..Z, 0..9 such as "_" and "!" and do it in base 64 instead. There's probably plenty of standard tools and convertors to do things in base 64. Best regards, Stuart Cooper.
Stuart Cooper wrote:
>> I'd like to convert very large unsigned numbers (ala bigint) to a text
>> string using base62. I created this PL/PERL function to do the trick:
>
> base 62 is cruel and unusual punishment. Introduce two more printing
> characters to your set a..z, A..Z, 0..9 such as "_" and "!" and do it in base 64
> instead. There's probably plenty of standard tools and convertors to do
> things in base 64.
I thought about adding 2 more characters, but I didn't like anything
that was on my keyboard ;-) In English, we use "0-9a-zA-Z" commonly but
to me, "-" and "_" look odd at the beginning or end of a string or when
repeated more than once.
Ugly code:
AR-____--_
OK code:
ARzd1A0b3P
In some cases, I may even want to eliminate characters that look similar
like "1" and "l" or "O" and "0". Better yet, if the code that comes out
of the conversion contains vowels, its possible to look like profanity:
PzbigAss22
So, perhaps a better character set would not include vowels either:
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
is reduced to:
23456789bcdfghjkmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ
# SELECT LENGTH('23456789bcdfghjkmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ');
length
--------
49
(1 row)
This looks like a job for Base49 conversion ;-)
The code I attached can do anywhere from base2 through base62 without
problems but uses a hard-coded replacement set of characters. This
could be modified to accept another parameter of replacement chars.
My original question is more along the lines of trying to see if there
were built-in functions in PostgreSQL that already do this type of base
conversion. As a built-in, my expectation would be that it would likely
be faster and supported ... without me having to introduce a PL/PERL
dependent custom function. Worst case, I could do the base conversion
using the default character mappings and just remap the output to the
alternate characters.
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM
23456789bcdfghjkmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ
0 => 2, 1 => 3. ... r => z, ... M => Z
Base conversion seems like a common task for most programming languages.
I didn't know where to look and it wasn't coming up in my searches.
-- Dante
On Wed, 14 May 2008, D. Dante Lorenso wrote: > Stuart Cooper wrote: >> base 62 is cruel and unusual punishment. Introduce two more printing >> characters to your set a..z, A..Z, 0..9 such as "_" and "!" and do it in >> base 64 >> instead. > > I thought about adding 2 more characters, but I didn't like anything that was > on my keyboard You really should use as much of an existing wheel here as possible rather than completely reinventing one: http://en.wikipedia.org/wiki/Base64 The standard additional two characters to add are "+/". > My original question is more along the lines of trying to see if there > were built-in functions in PostgreSQL that already do this type of base > conversion. http://www.postgresql.org/docs/current/static/functions-string.html shows an encode and decode pair that can use base64, you may be able to leverage those here. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD