Comparing two dates in a SQL request

Question

I am a beginner in PHP and Javascript, so I'm struggling to compare two dates in a SELECT request like this:

<?php
require 'db.php';
$sql = "SELECT * FROM shortcode WHERE invalid_from_shortcode < Date()";
$result = pg_query($sql);

if (!$result) {
    echo "Problem with query " . $query . "<br/>";
    echo pg_last_error();
    exit();
}
?>

I'm trying to select only the 'shortcodes' where the [invalid_from_shortcode] column is inferior from the current date.
I don't know how to get the current date and whether the structure is correct.

WHat has javascript got to do with this question – RiggsFolly Apr 25 '18 at 11:26 — RiggsFolly, Apr 25 '18 at 11:26
What kind of field is `invalid_from_shortcode` ? – bigwolk Apr 25 '18 at 11:26 — bigwolk, Apr 25 '18 at 11:26

pr1nc3 · Answer 1 · 2018-04-25T11:29:43.887

3

<?php
  require 'db.php';
  $sql = "SELECT * FROM shortcode WHERE invalid_from_shortcode < NOW()";
    $result = pg_query($sql);
    if (!$result) {
        echo "Problem with query " . $query . "<br/>";
        echo pg_last_error();
        exit();
    }
 ?>

Date() is a php function. in Mysql/PostgreSQL you must use NOW()

edited Apr 25 '18 at 11:29

answered Apr 25 '18 at 11:26

pr1nc3

8,108
3
23
36

seems that runs on PostgreSQL (pg_query...) – tanaydin Apr 25 '18 at 11:27
you are right. Thankfully they use the same function name. – pr1nc3 Apr 25 '18 at 11:29
You're welcome, glad i helped. Don't forget to mark my answer as accepted if it helped you. – pr1nc3 Apr 25 '18 at 11:33

score 0 · Answer 2 · answered Apr 25 '18 at 11:25

0

You want to use NOW() not DATE().

answered Apr 25 '18 at 11:25

Cuagau

548
3
7

score 0 · Answer 3 · answered Apr 25 '18 at 11:28

0

May be you are looking for CURDATE() .

 $sql = "SELECT * FROM shortcode WHERE DATE(invalid_from_shortcode) < CURDATE()";

answered Apr 25 '18 at 11:28

milan kumar

226
2
16

There is no `CURDATE()` in Postgres. – Erwin Brandstetter Apr 25 '18 at 12:39

Luca · Answer 4 · 2018-04-25T12:45:51.017

0

use

NOW()      Returns the current date and time

edited Apr 25 '18 at 12:45

answered Apr 25 '18 at 11:29

Luca

344
5
18

There is no `CURDATE()` in Postgres. – Erwin Brandstetter Apr 25 '18 at 12:39
I know. I didnt see `pg_query($sql)` :p – Luca Apr 25 '18 at 12:41

score 0 · Answer 5 · answered Apr 25 '18 at 11:32

0

You should be using NOW() function instead of date() and you need to convert the date in a suitable format enter link description here you can visit this link for converting the date format

answered Apr 25 '18 at 11:32

Parvez Khan

28
1
4

score 0 · Accepted Answer · answered Apr 25 '18 at 12:38

...
$sql = "SELECT * FROM shortcode WHERE invalid_from_shortcode < Date()"; 
...

A PHP functions like Date() is not visible in the SQL string. Either pass a value or use an equivalent Postgres function, like @pr1nc3 already suggested.

However, while you do not disclose the actual data type of invalid_from_shortcode it's unclear whether now() can serve as equivalent function. It returns the current timestamp with time zone, which is meaningfully different from the current date - effectively equivalent to the current timestamptz truncated to 00:00 according to the current timezone setting. You might need CURRENT_DATE (same as now()::date) instead. Related:

Select today's (since midnight) timestamps only

Aside: Unlike two other answers suggested, there is no ~~CURDATE()~~ in Postgres. That probably confusing MySQL with Postgres.

Comparing two dates in a SQL request

6 Answers6