PDO::PARAM for dates?

Question

Does some PDO::PARAM_??? exist which can be used for dates or timestamps?

Sample code:

$sql = "UPDATE my_table SET current_date = :date WHERE id = 43";
$statement = $pdo->prepare ($sql);
$statement->bindValue (":date", strtotime (date ("Y-m-d H:i:s")), PDO::PARAM_STR);
$statement->execute ();

score 115 · Accepted Answer · edited Sep 25 '12 at 04:09

115

When writing a date in an SQL query, you are writing it as a string; you have to do the same with prepared statements, and use PDO::PARAM_STR, like you did in the portion of code you proposed.

And for the "timestamp", if by "timestamp" you mean:

The MySQL timestamp data-type: it's the same, you'll pass it as a string
The PHP Unix timestamp, which is an integer: you'll pass it an int.

edited Sep 25 '12 at 04:09

Peter Mortensen

30,738
21
105
131

answered Mar 03 '10 at 20:24

Pascal MARTIN

395,085
80
655
663

1

What in case of ms precision? – ManuelSchneid3r Apr 04 '17 at 16:31
@ManuelSchneid3r, not for timestamps: "Unix time (also known as POSIX time or UNIX Epoch time) is a system for describing a point in time. It is the number of **seconds** that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, minus leap seconds." / https://en.wikipedia.org/wiki/Unix_time – Erk Nov 21 '18 at 00:15
Is easier that way, just define in the database the filed as VARCHAR(size) and send the variable with date("Y/m/d") with your time zone and if you want the hour date("h:i:s") – Ricardo Rivera Nieves Oct 07 '20 at 23:51
A date is fomatted as dd.mm. yyyy . Where I live at least. Storing dates as strings is not recommended as there is quite a risk they will be interpreted wrong. This might be the reason why the database engine angostic PDO library stayed clear of implementing a date or datetime value type. What might work in MySQL or MariaDB, might not in Progress or SQLServer. – theking2 Mar 02 '21 at 19:39
@theking2 nobody's talking about *storing* date as string here. Only about using the correct string representation in the SQL. This answer is all correct. – Your Common Sense Nov 30 '21 at 15:11

score 19 · Answer 2 · edited Jan 26 '19 at 13:10

19

Simply creating the date using php date function should fix this issue for you.

$handle->execute(array(":date"=>date("Y-m-d H:i:s", strtotime($date)), PDO::PARAM_STR));

Edit: Please note though, that strtotime (http://php.net/manual/en/function.strtotime.php) can't handle every kind of date formats.

edited Jan 26 '19 at 13:10

Jignesh Joisar

13,720
5
57
57

answered May 12 '14 at 11:27

Rolty

191
1
2

score 7 · Answer 3 · answered Mar 03 '10 at 20:21

7

Nope. Treat date as a string.

answered Mar 03 '10 at 20:21

Your Common Sense

156,878
40
214
345

Diego Andrade · Answer 4 · 2017-03-23T15:40:09.940

-2

You have to treat the date as string, but you can create a function to check if is a valid date before pass it as a param. Like this:

function checkValidDate($date, $format = "dd-mm-yyyy"){
            if($format === "dd-mm-yyyy"){
            $day = (int) substr($date,0,2);
            $month = (int) substr($date, 3,2);
            $year = (int) substr($date, 6,4);

        }else if($format === "yyyy-mm-dd"){
            $day = (int) substr($date,8,2);
            $month = (int) substr($date, 5,2);
            $year = (int) substr($date, 0,4);
        }

        return checkdate($month, $day, $year);
}

edited Mar 23 '17 at 15:40

answered Mar 23 '17 at 15:35

Diego Andrade

217
1
2
8

not the way to go, Diego. There are date format functions in every RDBMS I've encountered. Watch this: https://mariadb.com/kb/en/date_format/ – theking2 Dec 14 '20 at 15:34

theking2 · Answer 5 · 2021-11-30T15:31:56.110

-2

A complete section to properly store a DateTime value (as a string) in a RDBMS:

/** @const string app_date_format expected date format in the PHP domain (Swiss) */
define( 'app_date_format', 'd.m.Y' ); 

/** @var PDOConnection $db */
$db = new \PDO( $dsn, $db_user, $db_pass, $db_options );

/** @var DateTime $date */
$date = \DateTime::createFromFormat( app_date_format, '30.11.2020' );

$stmt = $db-> prepare(
    "UPDATE `test`
    SET `test_date` = STR_TO_DATE(:date, '%Y-%m-%d %H:%i:%s' )
    WHERE `test`.`test_id` = :id"
);

$id = 1;
$stmt->bindValue( ':id', $id );
$stmt->bindValue( ':date', $date-> format( 'Y-m-d H:i:s'));
$stmt->execute() or die( $stmt-> errorInfo()[2] );

Tested with PHP 7.4.25; MariaDB 10.6.4

edited Nov 30 '21 at 15:31

answered Dec 18 '20 at 15:37

theking2

2,174
1
27
36

the code is buggy and misleading. A lowercase constant is against the standard. And the answer doesn't even touch the question asked – Your Common Sense Dec 18 '20 at 17:10
@YourCommonSense Where are the bugs? Where are the standards? Where is the mislead. You need to elaborate Your Common Sense. – theking2 Mar 02 '21 at 19:34
@your-common-sense seriously. where? – theking2 Nov 29 '21 at 20:33
A year passed, and you still didn't learn it yourself? Well, that shameful `or die( $db-> error );` for example. But the main point, this STR_TO_DATE of yours is ridiculously redundant. The whole world around you makes it just `test_date = :date` as required by MySQL. – Your Common Sense Nov 30 '21 at 14:30
dates are not strings – theking2 Nov 30 '21 at 14:49
In SQL, they are. – Your Common Sense Nov 30 '21 at 14:54

score -5 · Answer 6 · edited Nov 12 '15 at 19:26

-5

This worked for me.

//MS SQL
$sql = "UPDATE my_table SET current_date = GETDATE() WHERE id = 43";
$statement = $pdo->prepare ($sql);
//$statement->bindParam (":date", strtotime (date ("Y-m-d H:i:s")), PDO::PARAM_STR);
$statement->execute ();

edited Nov 12 '15 at 19:26

ByteHamster

4,884
9
38
53

answered Nov 12 '15 at 19:09

hector teran

369
1
5
15

1

You are not helping much by not showing how to bind the param to a PHP-generated date instead of the mysql GETDATE function... – Florian Segginger Mar 01 '16 at 16:04

PDO::PARAM for dates?

6 Answers6

Linked