Skip site navigation (1) Skip section navigation (2)

Re: Coalesce with Timestamp,Nulls,and Concatenation

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Pam Wampler <Pam_Wampler(at)taylorwhite(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Coalesce with Timestamp,Nulls,and Concatenation
Date: 2002-04-17 19:57:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Thu, 2002-04-18 at 02:13, Pam Wampler wrote:
> can someone please give the correct syntax for a query that has a column
> timestamp that allows nulls and
> needs to be converted to a certain format and also needs to be concatenated
> with commas
> example:
> select to_char(timestamp1,'YYYYMMDD
> HH24:MI')||','||employee||','||to_char(timestamp2,'YYYYMMDD HH24:MI') from
> employee;
> How do I put the coalesce & also get the correct format and handle if the
> timestamps are nulls to ""?

Do you want the result of to_char coalesced to ""?

select coalesce( to_char(timestamp1,'YYYYMMDD HH24:MI'),'') || ',' ||
employee || ',' || coalesce( to_char(timestamp2,'YYYYMMDD HH24:MI'), '')
from employee;

Or do you want the null timestamp coalesced to some timestamp value?

select to_char( coalesce( timestamp1, 'now'::timestamp), 'YYYYMMDD
HH24:MI'),'') || ',' || employee || ',' || to_char( coalesce(
timestamp2, 'tomorrow'::timestamp), 'YYYYMMDD HH24:MI'), '') from

Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB:        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at yet?

In response to

pgsql-novice by date

Next:From: Brian ScandaleDate: 2002-04-17 20:30:43
Subject: Re: Interval data types and SQL Server
Previous:From: nandratsDate: 2002-04-17 18:26:06
Subject: C++ and postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group