[2.5.1] Comparing dates?

[Martin]

Hi,

2009/2/2 Gilles Ganault <nospam at nospam.com>:
> 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