How to select records from yesterday

1

Hello,

I'm trying to select rows from yesterday, here is my SQL request :

SELECT 
"mytable"."ID",
"mytable"."DISPLAYLABEL" as "Title",
"mytable"."EMSCREATIONTIME" as "Date/Hour of creation"
FROM "view_1"."mytable"
WHERE DATE("mytable"."EMSCREATIONTIME") = DATE_SUB(NOW(), INTERVAL 1 DAY)

I receive syntax error
I also tried :

SELECT 
"mytable"."ID",
"mytable"."DISPLAYLABEL" as "Title",
"mytable"."EMSCREATIONTIME" as "Date/Hour of creation"
FROM "view_1"."mytable"
WHERE DATE("mytable"."EMSCREATIONTIME") = DATE( NOW() - INTERVAL 1 DAY )

If I'm selecting rows from today, it's working fine using :

SELECT 
"mytable"."ID",
"mytable"."DISPLAYLABEL" as "Title",
"mytable"."EMSCREATIONTIME" as "Date/Hour of creation"
FROM "view_1"."mytable"
WHERE DATE("mytable"."EMSCREATIONTIME") = DATE( NOW() )

How can I acheive this ?

2

Hi @Exe
Without knowing which database type you are querying or the error you are seeing, then it's not possible to help.
Post "Diagnostic Info" from Admin > Troubleshooting.

3

Hello,

I'm trying to query PostgreSQL as Database.

{
  "browser-info": {
    "language": "fr-FR",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.64 Safari/537.36 OPR/87.0.4390.35",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "Cp1252",
    "java.runtime.name": "Java(TM) SE Runtime Environment",
    "java.runtime.version": "17.0.3.1+2-LTS-6",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "https://java.oracle.com/",
    "java.version": "17.0.3.1",
    "java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
    "java.vm.version": "17.0.3.1+2-LTS-6",
    "os.name": "Windows Server 2016",
    "os.version": "10.0",
    "user.language": "fr",
    "user.timezone": "Europe/Paris"
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MariaDB",
        "version": "10.6.5-MariaDB"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.7.5"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-05-16",
      "tag": "v0.43.1",
      "branch": "release-x.43.x",
      "hash": "7f1a1c4"
    },
    "settings": {
      "report-timezone": "Europe/Paris"
    }
  }
}
4

@Exe You have to use interval '1 day' as noted in the documentation: https://www.postgresql.org/docs/current/functions-datetime.html
Metabase will not make your SQL syntax correct, so if the syntax is wrong, then that is still just send to your database, which spits back an error.

5

Which one is best to use in production ?

  • WHERE DATE("mytable"."EMSCREATIONTIME") = DATE( NOW() - interval '1 day' )
  • WHERE DATE("mytable"."EMSCREATIONTIME") = CURRENT_DATE - 1
6

@Exe You can use EXPLAIN ANALYZE to see how your database handles query planning.
https://www.metabase.com/learn/sql-questions/sql-best-practices.html#explain
But probably the second one.

1 Like