#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    6
    Rep Power
    0

    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 ?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0
    Hi,

    You can make a query like this:

    Code:
    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')
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by alphak01
    You can make a query like this:
    That query is unnecessary complicated. It can be rewritten as:
    Code:
    SELECT * 
    FROM Calls 
    WHERE date_column BETWEEN  DATE '2013-01-01' AND DATE '2013-02-05'
    There is no need for all that date to string to date conversion if you use a standard date literal.

    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)

    Originally Posted by Nibur
    a column that is named date
    That is a bad choice for a column name. Because

    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.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo