This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Slash-pgsql mailing list
Subject: Re: [Slash-pgsql] I think we're done
From: Alessio Bragadini <alessio@albourne.com>
Date: Wed, 06 Dec 2000 17:14:38 +0200
the equiv of "SELECT to_days(now())" in postgresql
SELECT to_char(now(),'J')
returns the Julian day, i.e. "days since January 1, 4712 BC"
large number but unique...
village=# select to_char(now(),'J');
to_char
---------
2451885
(1 row)
mysql> select to_days(now());
+----------------+
| to_days(now()) |
+----------------+
| 730825 |
+----------------+
1 row in set (0.00
sec)
> I'm happy to take patches from anyone who wants to work
> on the port.
I hope to have soon time to try it.
===
Subject: Re: [Slash-pgsql] I think we're done
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
Date: Wed, 06 Dec 2000 16:33:58 +0000
the equiv of "SELECT to_days(now())" in postgresql
> mysql> select to_days(now());
> +----------------+
> | to_days(now()) |
> +----------------+
> | 730825 |
> +----------------+
> ... I hope to have soon time to try it.
create function to_days(date) returns integer as
'select cast(to_char($1,''J'') as integer)
- cast(to_char(date ''0001-01-01 BC'' as integer)'
language 'sql';
or, probably faster,
create function to_days(date) returns integer as
'select cast(((date_part(''epoch'',$1)
- date_part(''epoch'', date ''0001-01-01 BC'')) / 86400) as
integer)'
language 'sql';
(remember that there is no "year zero", which appears to be the
reference point for the MySQL function, hence the reference to "1 BC".)
I've got a MySQL schema porting script to contribute to the PostgreSQL
tree. How about collecting the above function and others into a
PostgreSQL contrib/mysql directory to help with porting? You could
extend your database installation with these functions, but it will
avoid clogging the basic PostgreSQL system with the large number of
nonstandard and redundant MySQL functions. For example, if you have used
what MySQL says are "ODBC functions" in your code, we already have a
contrib/odbc package which will help define them for you in the backend
(rather than in the ODBC driver where they are usually implemented).
btw, you may want to reimplement the above function in C code to speed
it up a bit, though if you are using it to display results (rather than
in a qualification clause) it is probably a "don't care". And even worst
case it will be hard to tell the difference between the above and a C
function.
===