Adding a variable interval to a variable timestamp

Question

Ok, first of all I have very little clue how to use php, and only a slightly better understanding of sql. So if anything I do appears really, really stupid, please bear with me.

Given a user input of a date, a time and a time interval, I need to get two timestamps without timezones - a starting time, and an endtime. e.g.

function myfunction($startdate, $starttime, $numhours){

$start = $startdate.' '.$starttime;
//I know this works for a timestamp because I have used this value in other sql statements and have been returned the correct results

$handler = $connection->prepare("SELECT TIMESTAMP :start + INTERVAL :numhours ' HOURS'");
$handler->bindParam(':start', $start);        
$handler->bindParam(':numhours', $numhours);
$handler->execute(); 
$end = $handler->fetchAll();

This just gets me the following error:

Submitting booking.SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1" LINE 1: SELECT TIMESTAMP $1 + INTERVAL $2 ' HOURS'

I haven't been able to find anything that really tells me what I'm doing wrong here (which is probably a lot). Help is appreciated.

EDIT: I'm using postgreSQL, through `pgAdmin III

EDIT: $start should have the form 'YYYY-MM-DD HH:MM:SS'

$start is meant to be a string of the form 'YYYY-MM-DD HH-MM-SS', which I've found works when searching for a timestamp in sql. $numhours is just an integer — HappyPrime
– HappyPrime, Commented May 26, 2014 at 11:37
Your interval literal has a syntax error too, use INTERVAL '3 hours' or INTERVAL '3' HOUR (instead of INTERVAL '3' ' HOURS') — pozs
– pozs, Commented May 26, 2014 at 11:42
Ok, but there's still an issue with the first variable (:start) which I can't work out — HappyPrime
– HappyPrime, Commented May 26, 2014 at 11:47

pozs · Accepted Answer · 2014-05-26 13:12:44Z

2

The problem is, you use the literal notation with values (parameters) and not with constants.

The literal notation only accepts contants, like:

SELECT TIMESTAMP '2014-05-25 14:29:59' + INTERVAL 3 HOUR;

It cannot accept values (expressions) other than constants (and bound parameters are not constants.):

-- this will throw a syntax error
SELECT TIMESTAMP CONCAT('2014-05-25', ' ', '14:29:59');

You can use the CAST form over the literal notation here:

$handler = $connection->prepare('SELECT CAST(:start AS TIMESTAMP) + ' .
                                'CAST(:numhours || \' hours\' AS INTERVAL)');
$handler->bindParam(':start', $start);        
$handler->bindParam(':numhours', $numhours);
$handler->execute();

EDIT: or, you can use PDO::ATTR_EMULATE_PREPARES to use real literals inside your query, but I believe postgres' own prepare functionality is better a choice.

answered May 26, 2014 at 13:12

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

3 Comments

HappyPrime Over a year ago

Could you explain what is happening in the second half of that? When you're casting :numhours.

pozs Over a year ago

@user3464331 || is the text concatenation operator in postgres. CAST hasn't got the option to specify only the HOUR part of the INTERVAL, so you need to feed it with '3 hours' or '03:00:00' (or anything the interval accepts) postgresql.org/docs/current/static/…

HappyPrime Over a year ago

Well sweet! Thanks for that, it works. I thought the bindParam put the variable in as a sort of string, but evidently I was mistaken.

Collectives™ on Stack Overflow

Adding a variable interval to a variable timestamp

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related