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

Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs?

From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
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-29 10:33:41
Message-ID: l03110703b286d2f9c46a@[] (view raw, whole thread or download thread mbox)
Lists: pgsql-general
At 13:12 +0200 on 27/11/98, Stuart Rison wrote:

> Again, this solves one part of my problem (i.e. the query functions even if
> I datetime is NULL) but what I'm looking for is something that ONLY shows
> the date portion of a datetime (e.g. Thu Nov 26 16:35:23 1998 MSK becomes
> 26-11-1998) so the date_trunc masking is not quite what I'm looking for.
> Would it be possible to do a select datetime and then output only the
> dateday, datemonth and dateyear using an RE??
> All the same, thanks for your suggestion Anatoly, UNION's the winner so
>far ;)

Well, first I'd like to say that not converting null datetimes is
definitely a bug and should be placed in the ToDo list for the next version.

That said, I tried several conversions, using the following table:

testing=> select * from test1;
f1          |dt
This year   |Fri Jan 02 00:00:00 1998 IST
Next year   |Mon Mar 15 00:00:00 1999 IST
Nothing     |
Last year   |Mon May 12 00:00:00 1997 IDT
More nothing|
(5 rows)

The one that finally worked is the following:

testing=> select f1, date( abstime( dt ) ) from test1;
f1          |      date
This year   |01-02-1998
Next year   |03-15-1999
Nothing     |
Last year   |05-12-1997
More nothing|
(5 rows)

That is, convert to abstime, and then to date...


Herouth Maoz, Internet developer.
Open University of Israel - Telem project

In response to

pgsql-general by date

Next:From: Anton de WetDate: 1998-11-29 17:05:49
Subject: Re: [GENERAL] equivalent of sqlload?
Previous:From: Herouth MaozDate: 1998-11-29 10:08:31
Subject: Re: [GENERAL] equivalent of sqlload?

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