Problems with parameterizing timestamp references in PostgreSQL in Perl

Question

I am trying to use a parameterized query in a Perl script to get some timestamps back from a Postgres database. Here's a cut-and-dried example, solely for pedagogical purposes.

I've defined $start_date and $end_date as timestamps and intervals:

my $start_date = "current_timestamp - interval '6 hours'";
my $end_date = "current_timestamp";

I use the following to submit to the database, with $dbh defined earlier:

my $sql = "SELECT cast(? as timestamp), cast(? as timestamp)";
my $sth = $dbh->prepare($sql);
$sth->execute($start_date, $end_date);

When I do this, I get a somewhat confusing error.

DBD::Pg::st execute failed: ERROR:  date/time value "current" is no longer supported

I understand that current hasn't been supported in PG since 7.2, but I'm not using that. I'm using current_timestamp, which is supported, AFACT. To wit, if I enter into psql:

select (cast(current_timestamp - interval '6 hours' as timestamp), cast(current_timestamp as timestamp));

the result is what I expect (two timestamps, the former six hours previous to the latter).

I could also use now() rather than current_timestamp. I can use it in the following way:

my $start_date = "now() - interval '6 hours'"; 
my $end_date = "now()";

When I try to run the query in perl, I get the following error:

DBD::Pg::st execute failed: ERROR:  invalid input syntax for type timestamp: "now() - interval '6 hours'"

Yet, the query:

select (cast(now() - interval '6 hours' as timestamp), cast(now() as timestamp));

gives me the expected result.

I am quite flummoxed.

I think the syntax is wrong...The correct way should be like this $sth->execute($sql); — Rudra
– Rudra, Commented Oct 17, 2013 at 18:32
The syntax isn't wrong; I'm using the SQL and the database handle to create a statement handle using prepare, and then I'm using execute to run it with the appropriate parameters. (I'm getting this both from existing (working) code in our codebase, along with the DBI documentation.) — techstepper
– techstepper, Commented Oct 17, 2013 at 18:43
possible duplicate of How can I use a query with placeholder inside quotes? (perl / postgresql) — ThisSuitIsBlackNot
– ThisSuitIsBlackNot, Commented Oct 17, 2013 at 19:18
@ThisSuitIsBlackNot, that's related, but not really a duplicate. — cjm
– cjm, Commented Oct 17, 2013 at 19:37
Do you intend $start and $end to be arbitrary pg expressions of timestamps? Or will they always be offsets from "now"? (And, if so, will the offsets always be in hours?) — pilcrow
– pilcrow, Commented Oct 17, 2013 at 19:43

cjm · Accepted Answer · 2013-10-17 19:31:31Z

5

The problem is that a SQL placeholder doesn't represent an expression, but a single value. And that value can't be a function. You could do something like:

my $start_date = "6 hours";
my $sql = "SELECT current_timestamp - cast(? as interval), current_timestamp";
my $sth = $dbh->prepare($sql);
$sth->execute($start_date);

What you're doing in Perl is equivalent to doing this in psql:

select (cast('current_timestamp - interval ''6 hours''' as timestamp), cast('current_timestamp' as timestamp));

edited Oct 17, 2013 at 19:31

answered Oct 17, 2013 at 18:38

cjm

62.2k9 gold badges133 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

techstepper Over a year ago

Thank you for your reply. I tried that, and I got the following error: DBD::Pg::st execute failed: ERROR: syntax error at or near "$1" and made no mention of $start_date or its contents (6 hours), which I did pass in. Trying it by setting $start_date = "'6 hours'"; yields the same error.

cjm Over a year ago

Apparently, PostgreSQL doesn't let you say INTERVAL ?, you have to say CAST(? AS INTERVAL). And the outer cast is redundant, because a timestamp minus an interval is already a timestamp.

pilcrow · Accepted Answer · 2013-10-17 20:55:38Z

1

To make the windows of your queries a bit more flexible:

$sth = $dbh->prepare(<<__eosql);
SELECT * FROM tbl
 WHERE ts BETWEEN current_timestamp - ? * CAST('1 ' || ? AS INTERVAL)
                  AND
                  current_timestamp;
__eosql

$sth->execute(6, 'hour');
$sth->execute(10, 'day');
$sth->execute(1, 'week');
# etc.

When you introduce fixed time points, you could do something too clever like ... WHERE COALESCE(?, current_timestamp) ... and remember that an undef parameter defaults to the current time. However, I'd probably write and prepare a separate query.

answered Oct 17, 2013 at 20:55

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

Collectives™ on Stack Overflow

Problems with parameterizing timestamp references in PostgreSQL in Perl

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related