Re: pgbench and timestamps

Hi,

Thanks for your comments, I worked around that problem because I was able
to truncate the timestamp and use only the date part , alsoit might
works the use of to_timestamp. But I would like to understand what is
happening , I realized that pgbench is identified erroneously the minutes
and seconds parts :00:00 as two variables .

Regards

El mié., 24 jun. 2020 a las 14:50, David Rowley (<dgrowleyml(at)gmail(dot)com>)
escribió:

> On Wed, 24 Jun 2020 at 20:41, Jaime Soler <jaime(dot)soler(at)gmail(dot)com> wrote:
> >
> > Hi, does anybody know what is wrong with pgbench in this case ?. Here is
> a simple query to generate a random date in a interval time.sql:
> >
> > (select timestamp '2005-09-01' + random() * ( timestamp '2006-03-01
> 00:00:00' - timestamp '2005-09-01 00:00:00' ));
> > pgbench -c 2 -j 2 -M prepared --file time.sql -h localhost -d picp -U
> postgres -p 5432
> > ERROR: la sintaxis de entrada no es válida para tipo timestamp:
> «2006-03-01 00$1$2»
> >
> > I don't know why pgbench use timestamp: «2006-03-01 00$1$2» instead of
> timestamp '2006-03-01 00:00:00'
>
> I've not debugged it, but it looks like pgbench thinks that :00 is a
> pgbench variable and is replacing each instance with a query
> parameter.
>
> https://www.postgresql.org/docs/12/pgbench.html says:
>
> "There is a simple variable-substitution facility for script files.
> Variable names must consist of letters (including non-Latin letters),
> digits, and underscores. Variables can be set by the command-line -D
> option, explained above, or by the meta commands explained below. In
> addition to any variables preset by -D command-line options, there are
> a few variables that are preset automatically, listed in Table 257. A
> value specified for these variables using -D takes precedence over the
> automatic presets. Once set, a variable's value can be inserted into a
> SQL command by writing :variablename. When running more than one
> client session, each session has its own set of variables. pgbench
> supports up to 255 variable uses in one statement."
>
> I don't often do much with pgbench and variables, but there are a few
> things that surprise me here.
>
> 1) That pgbench replaces variables within single quotes, and;
> 2) that we still think it's a variable name when it starts with a digit,
> and;
> 3) We replace variables that are undefined.
>
> I won't pretend to be familiar enough with pgbench internals to know
> if there's any reasonable reasons why we do each of the above, but...
>
> I guess you could work around this problem by just not putting the
> midnight time in your timestamp. However, that might not work so well
> if you want to specify a time other than midnight.
>
> David
>