From: | tolik(at)icomm(dot)ru (Anatoly K(dot) Lasareff) |
---|---|
To: | Stuart Rison <stuart(at)ludwig(dot)ucl(dot)ac(dot)uk> |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs? |
Date: | 1998-11-27 07:27:42 |
Message-ID: | x74srlzjbl.fsf@tolikus.hq.aaanet.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "SR" == Stuart Rison <stuart(at)ludwig(dot)ucl(dot)ac(dot)uk> writes:
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)"
. . .
SR> I find myself having to do two selects to get all the people in the table.
SR> i.e.:
SR> patients=> SELECT surname,firstname,othernames,dob::date FROM patients
SR> WHERE dob
SR> IS NOT NULL;
SR> surname|firstname|othernames | date
SR> -------+---------+--------------------+----------
SR> Goose |Mother |Lay Golden Eggs |11-01-1923
SR> One |Un |Uno Ein |11-11-1111
SR> Light |Dee |Full |22-01-1933
SR> (3 rows)
SR> patients=> SELECT surname,firstname,othernames FROM patients WHERE dob IS NULL;
SR> surname|firstname|othernames
SR> -------+---------+----------
SR> Rison |Stuart |
SR> Rison |This |Pal
SR> Rison |Mark |
SR> (3 rows)
SR> My question is, how do I get surname,firstname,othername and the date
SR> portion of ALL people in table people regardless of whether the entry has
SR> an actual dob or a NULL dob.
There are, on my mind, at least two answers. For experience I use
small table 'create table a( dt datetime, i int)'. Hera are data in
this table (one row has NULL as dt value):
tolik=> select * from a;
dt | i
----------------------------+--
Thu Nov 26 16:35:23 1998 MSK| 1
Wed Nov 25 00:00:00 1998 MSK| 2
Fri Nov 27 00:00:00 1998 MSK| 3
|10
First use 'union':
-----------------------------------------------
select dt::date, i from a where dt is not null
union
select NULL, i from a where dt is null;
date| i
----------+--
11-25-1998| 2
11-26-1998| 1
11-27-1998| 3
|10
(4 rows)
-----------------------------------------------
Second, try use date_trunc('day', dt) instead date_part:
--------------------------------------------------------------
tolik=> select date_trunc('day', dt), i from a;
date_trunc | i
----------------------------+--
Thu Nov 26 00:00:00 1998 MSK| 1
Wed Nov 25 00:00:00 1998 MSK| 2
Fri Nov 27 00:00:00 1998 MSK| 3
|10
(4 rows)
--------------------------------------------------------------
Regards!
--
Anatoly K. Lasareff Email: tolik(at)icomm(dot)ru
Senior programmer
From | Date | Subject | |
---|---|---|---|
Next Message | Wim Ceulemans | 1998-11-27 08:51:48 | Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs? |
Previous Message | The Hermit Hacker | 1998-11-27 03:45:05 | WWW based IMAP reader... |