PostgreSQL fetch data date range between

Databases
1

I am using PostgreSQL, I am facing an issue in that I am not able to fetch the record’s date range between
actually, in my database, I am saving this type of date in column “2022-12-21 16:12:15”
My PG Query is -
"SELECT * FROM table WHERE DATE_FORMAT(date_column,'%YYYY-%MM-%DD') BETWEEN '2022-12-21' AND '2023-03-20'"

SQL error:

ERROR:  function date_format(text, unknown) does not exist
LINE 1: ... WHERE DATE_FORMA...
                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How I solve this, Please help

2

If “date_column” is already a date field, you don’t need to format it - which is a TO_CHAR function, btw ( not what you want)

SELECT *
  FROM table_name
 WHERE date_column BETWEEN '2022-12-21' AND '2023-03-20'

ps - please do not use SELECT * unless you need EVERY field on a table. It’s inefficient and can be confusing later.

3

It not working, saying no row found, but the records exist in the database.

4

but OP indicated it has a time component

your syntax is still valid but will not return any rows from March 20 except those at precisely midnight

so there’s that

5

do you know how to set up a fiddle?

go to http://sqlfiddle.com, and click on the hamburger at the right of the header bar so that you can choose PostgreSQL as your database, then build a schema using CREATE TABLE that exactly mirrors your date_column, and then use INSERT statements to populate a few rows, making sure you include a few value both within and outside the range you want, then share the link with us so that we can see why the SQL suggested is not working

6

This is my fiddle http://sqlfiddle.com/#!17/eeb292/10

7

thank you

just curious, why did you use TEXT for date?

okay, you realise that this –

WHERE date BETWEEN '2022-12-21' AND '2022-12-21'

is guaranteed to return 0 rows from your data? the only possible value that satisfies the above condition is ‘2022-12-21 00:00:00’

try it like this –

WHERE date BETWEEN '2022-12-21' AND '2022-12-22'

note you do not have to convert the datetime to date (i’m not sure, but i think your TO_CHAR format string is incorrect)

you can just use date values like above, as long as you pick the right values

8

but this query is wrong, I expect the query will return 3 rows bcz from 21 to 23 there is 3 records exist in db, it show 1 records only.
overall there is one plus date is required to supply, that is wrong logically.
Please help…

9

actually it’s right, just wrong for your expectation :sunglasses:

let me show you how dates work with datetimes

when you compare a date (e.g. ‘2023-03-21’) to a datetime, it’s as though you had specified a datetime with a time of midnight (e.g. ‘2023-03-21 00:00:00’)

take this query for example –

WHERE date BETWEEN '2022-12-21' AND '2022-12-22'

this is equivalent to –

WHERE date BETWEEN '2022-12-21 00:00:00' AND '2022-12-22 00:00:00'

i will show you where your data plus your range endpoints are on this little diagram

2022-12-21 00:00:00  <-- 2022-12-21 ) 
2022-12-21 02:00:00                 )
2022-12-21 04:00:00                 )
2022-12-21 06:00:00                 )
2022-12-21 08:00:00                 ) WHERE date
2022-12-21 10:00:00                 )   BETWEEN '2022-12-21'
2022-12-21 12:00:00                 )       AND '2022-12-22'
2022-12-21 14:00:00                 )
2022-12-21 16:00:00    16:12        )
2022-12-21 18:00:00                 )
2022-12-21 20:00:00                 )
2022-12-21 22:00:00                 )
2022-12-22 00:00:00  <-- 2022-12-22 ) 
2022-12-22 02:00:00
2022-12-22 04:00:00
2022-12-22 06:00:00
2022-12-22 08:00:00
2022-12-22 10:00:00
2022-12-22 12:00:00   12:12
2022-12-22 14:00:00   13:11
2022-12-22 16:00:00
2022-12-22 18:00:00
2022-12-22 20:00:00
2022-12-22 22:00:00
2022-12-23 00:00:00  <-- 2022-12-23

can you see now why the query returns only 1 row of your sample data, and not the other two? it’s because the other two are beyond the upper endpoint of the range that you specified

tip: it’s better not to use BETWEEN with dates and datetimes, but instead, use a fixed lower bound and open upper bound like this –

WHERE date >= '2022-12-21' 
  AND date  < '2022-12-23'

this will pick up all datetimes for the 21st and also the 22nd

the reason this is such a great tip is what happens in February

WHERE date >= '2023-02-01' 
  AND date  < '2023-03-01'
10

but why is this function working in phpmyadmin and MySQL, there is no concern about the date range 21-22 there is just input 21-21 it works.
It might be due to DATE_FORMATE() is there an option same as we can do in PostgreSQL?

What is the problem ? actually I was working on phpmyadmin and this database is not able to handle my big database records as usually very slow speed for calculation, fetch and etc.
so I planned to switch to PostgreSQL here this DATE_FORMATE() not working, I am confused in MYSQL I used UNION, and JOINING with two different database tables the same query will run on PostgreSQL OR not…

11

yes it will

12

Even if I like Postgre more then mySQL there is no increase of speed if you use the same queries on postgre instead of mySQL. Postgre is definitely not faster then MySQL if you do not know how to use the advantages

13

Please help me to make this decision, that I am switching phpmyadmin to PostgreSQL a good decision or not.

14

To be honest, you don’t get a better lumberjack by changing your handsaw by a chainsaw….

First step is to learn the basics and the most important is to not store dates and/or times in text or varchar fields.
After you are able to choose the right column types for the data you need, it is a good idea to even know (and respect) as a minimum the first three normalization rules

After that you can start to analyze your queries with the explain command to see what is slow and what can be improved.

I have developed applications with both databases and at the end both are running fine if you know how to store and retrieve the data correctly.

15

i’d like to add that your performance problems might be resolved easily by good indexing

see https://use-the-index-luke.com/

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.