[2.5.1] Comparing dates?

Discussion in 'Python' started by Gilles Ganault, Feb 2, 2009.

  1. Hello

    I have data in an SQL database where one column contains a date
    formated as DD/MM/YYYYY.

    I need to select all rows where the date is before, say Feb 1st 2009,
    ie. 01/02/2009.

    Is there a command in Python that does this easily, or should I look
    into whatever date() function the SQL database offers?

    Thank you.
     
    Gilles Ganault, Feb 2, 2009
    #1
    1. Advertising

  2. Gilles Ganault

    John Machin Guest

    Re: Comparing dates?

    On Feb 2, 7:29 pm, Gilles Ganault <> wrote:
    > Hello
    >
    >         I have data in an SQL database where one column contains a date
    > formated as DD/MM/YYYYY.
    >
    > I need to select all rows where the date is before, say Feb 1st 2009,
    > ie. 01/02/2009.
    >
    > Is there a command in Python that does this easily, or should I look
    > into whatever date() function the SQL database offers?


    It would help if you told us:
    (1) Which database software
    (2) What type that column was declared to be in the CREATE TABLE
    statement
    (3) Whether you have used the Python DB API on a database before
    (4) Whether you have used a stand-alone SQL query tool on a database
    before
    (5) How you came to the conclusion that one column contains a date
    formatted as dd/mm/yyyy ... Only one? Are there other date-containing
    columns in the same table or other tables in the database? If so, how
    are they formatted?

    Basically, you are going to have to execute some SQL like:
    SELECT * FROM the_table WHERE the_date < '2009-02-01'
    which a reasonable chance of being syntactically valid and giving the
    correct answer, if the column type is DATETIME/DATE/TIMESTAMP/similar.
    If it's CHAR/VARCHAR/TEXT/CHARACTER/similar, then it's going to be
    harder, and will depend on what database software it is.

    A really silly question: have you asked the database administrator
    (DBA)?
     
    John Machin, Feb 2, 2009
    #2
    1. Advertising

  3. On Mon, 02 Feb 2009 20:06:02 +1100, Ben Finney
    <> wrote:
    >The Python data types for date and time are in the ‘datetime’ module
    ><URL:http://www.python.org/doc/2.6/library/datetime>. Create a
    >‘datetime’ object for each value you want, then compare them.


    Thanks guys. For those interested, here's how to perform the
    conversion from DD/MM/YYYY to YYYY-MM-DD:

    =====
    import datetime

    connection = datetime.datetime.strptime("21/02/2008",
    "%d/%m/%Y").strftime("%Y-%m-%d")
    print connection
    =====
     
    Gilles Ganault, Feb 2, 2009
    #3
  4. Gilles Ganault

    John Machin Guest

    Re: Comparing dates?

    On Feb 2, 10:07 pm, Gilles Ganault <> wrote:
    > On Mon, 02 Feb 2009 20:06:02 +1100, Ben Finney
    >
    > <> wrote:
    > >The Python data types for date and time are in the ‘datetime’ module
    > ><URL:http://www.python.org/doc/2.6/library/datetime>. Create a
    > >‘datetime’ object for each value you want, then compare them.

    >
    > Thanks guys. For those interested, here's how to perform the
    > conversion from DD/MM/YYYY to YYYY-MM-DD:
    >
    > =====
    > import datetime
    >
    > connection = datetime.datetime.strptime("21/02/2008",
    > "%d/%m/%Y").strftime("%Y-%m-%d")
    > print connection




    Gilles, that's certainly one way of doing it in Python.

    Here's another:

    >>> '-'.join(reversed('21/02/2008'.split('/')))

    '2008-02-21'

    Please consider the following:
    If in fact the database has a text-type column (VARCHAR or similar)
    that contains dates formatted as DD/MM/YYYY:

    (1) somebody should be talking rather bluntly to the database
    designer, and that column should be fixed if possible without breaking
    other code

    (2) doing the comparison on the client side (i.e. in Python) instead
    of on the server side (i.e. in SQL) means that there will be
    unnecessary data transmitted to the client side -- hence this should
    only be considered if the volume of data is small. Imagine trying to
    do a relational join using that column and another (normal) date
    column by sucking both tables down to the client!

    (3) it's quite possible to do the comparison in SQL:

    e.g. if the column is named "d":

    WHERE SUBSTR(d, 7, 4) || SUBSTR(d, 4, 2) || SUBSTR(d, 1, 2) <
    '20090201' -- insert '-' if preferred
    or something similar should be doable in any SQL implementation. Most
    will have functions like str[pf]time that could be used to similar
    effect.

    Cheers,
    John
     
    John Machin, Feb 2, 2009
    #4
  5. Re: Comparing dates?

    > Most
    > will have functions like str[pf]time that could be used to similar
    > effect.


    In mysql this is:
    str_to_date( '21/02/2008', '%d/%m/%Y')

    and oracle:
    to_date( '21/02/2008', 'dd-mm-yyyy')

    Cheers,
     
    Jervis Whitley, Feb 2, 2009
    #5
  6. Gilles Ganault

    Martin Guest

    Hi,

    2009/2/2 Gilles Ganault <>:
    > Thanks guys. For those interested, here's how to perform the
    > conversion from DD/MM/YYYY to YYYY-MM-DD:


    as suggested, the DBA should seriously think about defining the
    correct type of the column here, for intermediate use and getting
    stuff to work you could use a view and define some stored procedures
    on it so that inserting properly works...

    ---snip plain psql---
    test=# CREATE table date_test(
    id serial primary key,
    the_date timestamp with time zone,
    stuff Text
    );
    NOTICE: CREATE TABLE will create implicit sequence "date_test_id_seq"
    for serial column "date_test.id"
    NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
    "date_test_pkey" for table "date_test"
    CREATE TABLE
    test=# INSERT INTO date_test (the_date) VALUES ('20091231');
    INSERT 0 1
    test=# INSERT INTO date_test (the_date) VALUES ('20081231');
    INSERT 0 1
    test=# INSERT INTO date_test (the_date) VALUES ('20071231');
    INSERT 0 1
    test=# SELECT * from date_test;
    id | the_date | stuff
    ----+------------------------+-------
    1 | 2009-12-31 00:00:00+01 |
    2 | 2008-12-31 00:00:00+01 |
    3 | 2007-12-31 00:00:00+01 |
    (3 rows)
    ---snap plain psql---

    ---snip now in python---
    from datetime import datetime
    d = datetime(day=21, month=21, year=2008) # use a real datetime
    without string fiddling
    import psycopg2
    db = psycopg2.connect(host='localhost', user='test', password='test',
    database='test')
    cursor = db.cursor()
    cursor.execute("select * from date_test where the_date < '20080221'")
    # OK simple SELECT
    for row in cursor:
    print row
    # (3, datetime.datetime(2007, 12, 31, 0, 0,
    tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x959fe0c>), None)
    ## kill SQL injection stuff, also personally I find this more
    convenient that fiddling with strings...
    cursor.execute("select * from date_test where the_date < %s", (d, ))
    for row in cursor:
    print row
    (3, datetime.datetime(2007, 12, 31, 0, 0,
    tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x959fe8c>), None)
    ---snap now in python---



    --
    http://soup.alt.delete.co.at
    http://www.xing.com/profile/Martin_Marcher
    http://www.linkedin.com/in/martinmarcher

    You are not free to read this message,
    by doing so, you have violated my licence
    and are required to urinate publicly. Thank you.

    Please avoid sending me Word or PowerPoint attachments.
    See http://www.gnu.org/philosophy/no-word-attachments.html
     
    Martin, Feb 2, 2009
    #6
  7. On Mon, 2 Feb 2009 22:00:53 +0100, Martin <> wrote:
    >as suggested, the DBA should seriously think about defining the
    >correct type of the column here, for intermediate use and getting
    >stuff to work you could use a view and define some stored procedures
    >on it so that inserting properly works...


    Right, but SQLite only has two types: Numeric or text, so I figured
    it'd be better to turn dates into the usual YYYY-MM-DD before saving
    data into an SQLite file.

    Thanks for the feedback.
     
    Gilles Ganault, Feb 4, 2009
    #7
  8. En Wed, 04 Feb 2009 14:11:07 -0200, Gilles Ganault <>
    escribió:
    > On Mon, 2 Feb 2009 22:00:53 +0100, Martin <> wrote:


    >> as suggested, the DBA should seriously think about defining the
    >> correct type of the column here, for intermediate use and getting
    >> stuff to work you could use a view and define some stored procedures
    >> on it so that inserting properly works...

    >
    > Right, but SQLite only has two types: Numeric or text, so I figured
    > it'd be better to turn dates into the usual YYYY-MM-DD before saving
    > data into an SQLite file.


    I'd use the built-in adapter/converter instead of rolling my own:
    http://docs.python.org/library/sqlite3.html#default-adapters-and-converters

    --
    Gabriel Genellina
     
    Gabriel Genellina, Feb 9, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    893
    Rob Schieber
    Sep 30, 2005
  2. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    221
    Mark Schupp
    Aug 9, 2004
  3. Replies:
    1
    Views:
    226
    Jano Svitok
    Jul 17, 2007
  4. kellygreer1

    RFC-822 dates into Ruby dates

    kellygreer1, Jun 8, 2008, in forum: Ruby
    Replies:
    1
    Views:
    226
    Eric I.
    Jun 8, 2008
  5. Dr J R Stockton

    FAQ Dates; Opera Dates.

    Dr J R Stockton, Jun 18, 2007, in forum: Javascript
    Replies:
    0
    Views:
    177
    Dr J R Stockton
    Jun 18, 2007
Loading...

Share This Page