postgressql date format

am1

Hi,

Im using php with postgressql db.

i have a table full of peoples names and when they started.
in the form of

| SUE | 22/10/2002 18:20:44 |

i need to return all the people who started after a certain date.

can anyone please help?

thanks in advance

tomhath

Translate the strings you have into a UNIX Timestamp. THen everything is an integer:

http://www.phpbuilder.com/manual/function.strtotime.php

Sxooter

Originally posted by am1
Hi,

Im using php with postgressql db.

i have a table full of peoples names and when they started.
in the form of

| SUE | 22/10/2002 18:20:44 |

i need to return all the people who started after a certain date.

can anyone please help?

thanks in advance

AM

OK, assuming that the pipes above show the break point between your columns, i.e. the date is in a particular column, you can do something as simple as:

'select * from table where datefiled>'20 june 2002 00:00:00'

or something similar to get a listing after a certain date. You do have those all broken out into seperate fields, right?

am1

yes, but the format of the date field is 21042001...which is the 21st of the 4th.
the problem is that when i choose to select from >21042001 it gives me dates like
22012001
22022001
which aren't greater than 21042001

is there any way to get around this problem?

thanks
am

Sxooter

Originally posted by am1
yes, but the format of the date field is 21042001...which is the 21st of the 4th.
the problem is that when i choose to select from >21042001 it gives me dates like
22012001
22022001
which aren't greater than 21042001

is there any way to get around this problem?

thanks
am

OK, I take it those are not stored in a date field? You need to convert them to dates and store them in a date field (like god intended when he invented the date data type. :-)

then, you can use a select like so:

select * from table where date > '25 december 2001';

or something similar.

Storing dates in anything but date fields is a bad idea, as it makes your life harder. Let the database do its job and handle this sort of thing.

ultraslacker

use the to_date function for the conversion

to_date(datefield, 'DDMMYYYY')

Sxooter

Originally posted by ultraslacker
use the to_date function for the conversion

to_date(datefield, 'DDMMYYYY')

Yeah with that you can do it this way:

select field1, field2, field3, to_date(datefield,'DDMMYYY')::date, field5, fieldn into newtable from oldtable

to convert your data.