51

I'm a SQL developer and spend most of my time in MSSQL. I'm looking for a better way to filter a "Timestamp without timezone" field in a PostgreSQL DB.

I'm using:

Where 
DateField >= '2010-01-01' and 
DateField < '2012-01-01'

But given that I'm not an expert at the syntax I have to think there's a better way.

Any Suggestions? Thanks.

Espoir Murhabazi
  • 5,973
  • 5
  • 42
  • 73
Rob S
  • 658
  • 1
  • 5
  • 7
  • 2
    What's wrong with that anyway? You could use `between 2010-01-01` and 2012-01-01`, but it's almost the same, really. Is there a better way of doing this with MSSQL (i've never used it)? – Andre Apr 04 '13 at 17:42
  • 1
    @Andre It is better to explicitly specify the date format. – Ihor Romanchenko Apr 04 '13 at 17:48
  • It's fine, it's just running long. I made sure there was an index on the field, but it's just taking much longer than I'm used to. – Rob S Apr 04 '13 at 17:49
  • I've never had problems with comparing dates like that and I normally don't index my dates. @RobS are you working with ridiculously large amounts of data? Are you joining a bunch of tables together? – Andre Apr 04 '13 at 18:03
  • One table that I inherited. I'm expecting around 3-4 Mil rows returned. – Rob S Apr 04 '13 at 18:08

5 Answers5

89

Your solution is fine. If the dates are literals, I'd prefer, though:

WHERE datefield >= '2010-01-01 00:00:00' 
 AND  datefield <  '2012-01-01 00:00:00'

This performs exactly the same, but is more maintenable, because it makes clear the point of each literal "date" being a timestamp, not a date. For example, suppose sometime someone changes your query to the following

    AND  datefield <=  '2012-01-01'

... expecting (and failing) to include the full day "2012-01-01" in the query. With the later syntax, the intention is more clear and this confusion is prevented.

To make it even more clear (perhaps too verbose), you can do the explicit cast:

WHERE datefield >= '2010-01-01 00:00:00'::timestamp 
 AND  datefield <  '2012-01-01 00:00:00'::timestamp

I wouldn't use to_date() here for similar reasons (potential datatype confusion), nor to_timestamp() (it returns a timestamptz).

BTW, I've modified the case to comply with recommended practice (keywords in uppercase, identifiers in lowercase)

leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • 3
    You can use BETWEEN if you will use --your example-- '2010-01-01 00:00:00'::timestamp as the beginning date-time and '2010-01-01 24:00:00'::timestamp as the end. – thisfeller Apr 04 '13 at 19:24
17

For date intervals you can use something like:

WHERE DateField BETWEEN to_date('2010-01-01','YYYY-MM-DD') 
                    AND to_date('2010-01-02','YYYY-MM-DD')

It is shorter (you do not need to repeat DateField), and has explicit date format.

For 1 hour/day/month/year you can use:

WHERE date_trunc('day',DateField) = to_date('2010-01-01','YYYY-MM-DD')
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
4

You can keep the query simple by using BETWEEN as long as your column name is of type TIMESTAMP and your column name isn't "timestamp"...

SELECT * FROM table WHERE column BETWEEN '2018-12-30 02:19:34' AND '2018-12-30 02:25:34'

This works for dates '2018-12-30' and date-times '2018-12-30 02:19:34'.

Max Phillips
  • 6,991
  • 9
  • 44
  • 71
4

I would agree with leonbloy that using this would make the code more readable and clear.

WHERE datefield >= '2010-01-01 00:00:00'::timestamp 
AND  datefield <  '2012-01-01 00:00:00'::timestamp
Anish Jain
  • 509
  • 5
  • 12
2

You can also use interval if you want to filter for months or days etc. like this. datefield < current_date + interval '1 months'

shyam yadav
  • 214
  • 1
  • 10