February 5th, 2013, 04:27 AM
How do i make a selection on timestamp without time zone?
I have a table named Calls whit a column that is named date (timestamp without time zone) the entry in database looks like this: 2011-11-25 12:56:30.383
How do i make a select when i want to get all calls that is for example called between 2011-11-04 and 2011-12-11 ?
February 5th, 2013, 06:55 AM
You can make a query like this:
SELECT * FROM Calls WHERE date BETWEEN to_date(to_char('2013-01-01'::timestamp, 'YYYY/MM/DD'), 'YYYY/MM/DD') AND to_date(to_char('2013-02-05'::timestamp, 'YYYY/MM/DD'), 'YYYY/MM/DD')
February 5th, 2013, 07:21 AM
That query is unnecessary complicated. It can be rewritten as:
Originally Posted by alphak01
There is no need for all that date to string to date conversion if you use a standard date literal.
WHERE date_column BETWEEN DATE '2013-01-01' AND DATE '2013-02-05'
DATE '2013-01-01' is a "date literal". Due to the "DATE" keyword, no format mask is required (because the date literal is defined as using the ISO date format YYYY-MM-DD)
That is a bad choice for a column name. Because
Originally Posted by Nibur
a) it doesn't mean anything. It doesn't tell anyone looking at your database what it is actually about. Is that a "registration date", an "end date", a "due date", a "start date", ....?
b) date is a reserved word and should not be used for identifiers.
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions: