[2.5.1] Comparing dates?

[Gilles Ganault]

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.

 

[John Machin]

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)?

 

[Gilles Ganault]

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
=====

 

[John Machin]

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:
'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

 

[Jervis Whitley]

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,

 

[Martin]

Hi,

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

 

[Gilles Ganault]

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.

 

[Gabriel Genellina]

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