PSQL: Syntax error when passing set date variable into datetime

Question

I'm working server-side (using SSH) in psql, where I set variables using the '\set' command.

My concern is this: I have a query where I'm inserting a set string (date) into a datetime, but I get thrown a syntax error.

Simple example:

SELECT
    network_no,
    program_no,
    national_datetime.
FROM
    views
WHERE
    national_datetime
    between ':from_date 06:00:00'
    and ':to_date 05:59:59'::timestamp + '1 day'::interval

Where 'from_date' and 'to_date' have been set accordingly:

\set from_date 2017-07-10
\set to_date 2017-07-16

I know normally with dates you're supposed to set three sets of apostrophes to read it in correctly (I never looked up the reason), but I'm feeding this date into a datetime, so I figured using no apostrophes would work, but I get thrown this error:

ERROR:  invalid input syntax for type timestamp: ":from_date 06:00:00"
LINE 40: between ':from_date 06:00:00'

Normally in a bash script this would work since it just passes the literal string value, but in this case, whether I put no or one set of apostrophes around the date values it won't pass the value (I'm assuming because of the way PSQL handles set variables.

I know there's ways around this, but I'm looking for a reason why something like this would be happening, and whether there's a simple way to fix the "invalid input syntax"--whether it be through casting a variable, setting the variable differently, etc.

Many thanks in advance!

Hogan · Accepted Answer · 2017-08-11 17:34:54Z

1

:from_date is a variable that is not a string.

Here is a way to do it:

 date :from_date + time ' 06:00:00'

see doc at https://www.postgresql.org/docs/9.2/static/functions-datetime.html

answered Aug 11, 2017 at 17:34

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

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

1 Comment

swagardson Over a year ago

This worked perfectly. One thing to note for anybody else reading this is that you'll still need to do the triple set of apostrophes when setting a variable (ex: \set from_date '''2017-07-10''') to have it read in correctly. Additionally, the resulting type from the above answer will implicitly be calculated as a timestamp, so if you have interval logic to add to that as well, you can just add something like (+ '1 day'::interval) to the equation as well without explicit typecasting.

Collectives™ on Stack Overflow

PSQL: Syntax error when passing set date variable into datetime

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related