pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[SQL] Typecasting datetype as date. How do I cope with NULLs?
2+ messages / 2 participants
[nested] [flat]
* [SQL] Typecasting datetype as date. How do I cope with NULLs?
@ 1998-11-26 15:55 Stuart Rison <stuart@ludwig.ucl.ac.uk>
1998-11-27 12:23 ` Re: [SQL] Typecasting datetype as date. How do I cope with NULLs? Sferacarta Software <sferac@bo.nettuno.it>
0 siblings, 1 reply; 2+ messages in thread
From: Stuart Rison @ 1998-11-26 15:55 UTC (permalink / raw)
To: pgsql-sql; +Cc: stuart@ludwig.ucl.ac.uk
Dear All,
For those of you who don't want to wade through the details, here's the
question: "How do I get the date portion of a datetime field for ALL
ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
Typecasting a datetime NULL as date generates an error)"
details...
I have a table which stores, among other information, people's Date of
Birth as a datetime datatype called dob (I don't use the 'time' part but
the datatype is supported by many more functions). Of course, I don't
always know the birthday of the person involved so the filed sometimes has
a NULL value.
patients=> \d patients
Table = patients
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| patient_id | char() not null | 16 |
| surname | text | var |
| firstname | text | var |
| othernames | text | var |
| dob | datetime | 8 |
| sex | char() | 1 |
+----------------------------------+----------------------------------+-------+
Index: patients_new_pkey
patients=>
I have a select on this table which typecasts the dob (Date of Birth) as
date i.e.:
patients=> SELECT surname,firstname,othernames,dob::date FROM patients;
ERROR: Unable to convert null datetime to date
patients=>
I find myself having to do two selects to get all the people in the table.
i.e.:
patients=> SELECT surname,firstname,othernames,dob::date FROM patients
WHERE dob
IS NOT NULL;
surname|firstname|othernames | date
-------+---------+--------------------+----------
Goose |Mother |Lay Golden Eggs |11-01-1923
One |Un |Uno Ein |11-11-1111
Light |Dee |Full |22-01-1933
(3 rows)
patients=> SELECT surname,firstname,othernames FROM patients WHERE dob IS NULL;
surname|firstname|othernames
-------+---------+----------
Rison |Stuart |
Rison |This |Pal
Rison |Mark |
(3 rows)
My question is, how do I get surname,firstname,othername and the date
portion of ALL people in table people regardless of whether the entry has
an actual dob or a NULL dob.
The best I have managed so far is:
patients=> SELECT surname,firstname,othernames,date_part('day',dob) as dd,
patients-> date_part('month',dob) as mm, date_part('year',dob) as yyyy
patients-> FROM patients;
surname|firstname|othernames |dd|mm|yyyy
-------+---------+--------------------+--+--+----
Goose |Mother |Lay Golden Eggs |11| 1|1923
One |Un |Uno Ein |11|11|1111
Light |Dee |Full |22| 1|1933
Rison |Stuart | | | |
Rison |This |Pal | | |
Rison |Mark | | | |
(6 rows)
patients=>
But I would like to have the date of birth as one field rather than 3. I
have tried concatenating with || (no joy, date_part returns a float8 so
can't cat) and textcat (same problem). Typecasting the return value of
date_part does not work (e.g.:
patients=> select date_part('day',dob)::text ||
date_part('month',dob)::text fro
m patients;
?column?
--------------------------------------------------------
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:01 2000 GMT
Sat 01 Jan 00:00:01 2000 GMTSat 01 Jan 00:00:04 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
(6 rows)
patients=> select
textcat(date_part('day',dob)::text,date_part('month',dob)::text);
same output!
CAN IT BE DONE???
cheers,
Stuart.
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk |
+-------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Typecasting datetype as date. How do I cope with NULLs?
1998-11-26 15:55 [SQL] Typecasting datetype as date. How do I cope with NULLs? Stuart Rison <stuart@ludwig.ucl.ac.uk>
@ 1998-11-27 12:23 ` Sferacarta Software <sferac@bo.nettuno.it>
0 siblings, 0 replies; 2+ messages in thread
From: Sferacarta Software @ 1998-11-27 12:23 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Stuart Rison <stuart@ludwig.ucl.ac.uk>
Hello Stuart,
giovedì, 26 novembre 98, you wrote:
SR> Dear All,
SR> For those of you who don't want to wade through the details, here's the
SR> question: "How do I get the date portion of a datetime field for ALL
SR> ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
SR> Typecasting a datetime NULL as date generates an error)"
Table = a
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| dt | datetime | 8 |
| i | int4 | 4 |
+----------------------------------+----------------------------------+-------+
prova=> select * from a;
dt |i
----------------------+-
1998-12-26 16:35:23+01|1
1998-12-25 00:00:00+01|2
1998-12-27 00:00:00+01|3
(3 rows)
prova=> insert into a values (null,4);
INSERT 553697 1
prova=> select * from a;
dt |i
----------------------+-
1998-12-26 16:35:23+01|1
1998-12-25 00:00:00+01|2
1998-12-27 00:00:00+01|3
|4
(4 rows)
prova=> select *, extract(day from dt) from a;
dt |i|date_part
----------------------+-+---------
1998-12-26 16:35:23+01|1| 26
1998-12-25 00:00:00+01|2| 25
1998-12-27 00:00:00+01|3| 27
|4|
(4 rows)
-Jose'-
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-11-27 12:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-26 15:55 [SQL] Typecasting datetype as date. How do I cope with NULLs? Stuart Rison <stuart@ludwig.ucl.ac.uk>
1998-11-27 12:23 ` Sferacarta Software <sferac@bo.nettuno.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox