[SQL] Auto-formatting timestamps?

[Peter Koczan]

Hi all,

I'm undergoing a port from an old Sybase database to Postgres. It's
going surprisingly well, but I have a question regarding formatting of
timestamps.

In Sybase, we get:
:> select date from checkoutdate;
date
'May  1 2001 12:00AM'
...

In Postgres:
=> select date from checkoutdate;
   date
-
 2001-05-01 00:00:00
...

I can properly format it using to_char:
=> select to_char(date, 'Mon DD  HH:MIAM') as date from checkoutdate;
   date
-
 May 01 2001 12:00AM
...

Short of creating a wrapper type for timestamp (which seems like
overkill just for modifying the output function), is there a way to
output the Sybase format automatically (i.e. without a call to
to_char)?

I've found some code that actually somewhat depends on this format,
and one of my goals in this port is to change as little client code as
possible. Is it possible to automatically change the output like this,
preferably on a per-connection basis? I found stuff regarding the
datestyle parameter in the docs, but that doesn't quite do what I'd
like.

Thanks much,
Peter

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Auto-formatting timestamps?

[Mag Gam]

Why not create a view?



On Tue, May 13, 2008 at 11:58 AM, Peter Koczan <[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I'm undergoing a port from an old Sybase database to Postgres. It's
> going surprisingly well, but I have a question regarding formatting of
> timestamps.
>
> In Sybase, we get:
> :> select date from checkoutdate;
> date
> 'May  1 2001 12:00AM'
> ...
>
> In Postgres:
> => select date from checkoutdate;
>   date
> -
>  2001-05-01 00:00:00
> ...
>
> I can properly format it using to_char:
> => select to_char(date, 'Mon DD  HH:MIAM') as date from checkoutdate;
>   date
> -
>  May 01 2001 12:00AM
> ...
>
> Short of creating a wrapper type for timestamp (which seems like
> overkill just for modifying the output function), is there a way to
> output the Sybase format automatically (i.e. without a call to
> to_char)?
>
> I've found some code that actually somewhat depends on this format,
> and one of my goals in this port is to change as little client code as
> possible. Is it possible to automatically change the output like this,
> preferably on a per-connection basis? I found stuff regarding the
> datestyle parameter in the docs, but that doesn't quite do what I'd
> like.
>
> Thanks much,
> Peter
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] Add a ROWCOUNT to the output of a select.

[Gavin 'Beau' Baumanis]

Hi Everyone,

After spending some time searching through our good friend Mr. Google  
and the mailing list I found a post that provided me with a query that  
does just what I need.


However, the query takes FOREVER and although this is stated in the  
original mail I thought I would ask if there was any advice that you  
might be able to provide to speed things up a little.


And while the query does return over 27,000 rows in my case, I didn't  
expect it to take 9 minutes and 11 seconds!


Please find the query below - and of course - thanks in advance for  
any assistance you might be able to provide me with!


select
(select
 count(*)
 from
 myTable as myCount
 where
 myCount.contactdate <= myTable.contactdate
) as rownum,
contactdate
from
myTable
where
contactdate > '2007-06-30 23:59:59'
order by
contactdate;


-Gavin

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Add a ROWCOUNT to the output of a select.

[Gurjeet Singh]

On Wed, May 14, 2008 at 5:24 AM, Gavin 'Beau' Baumanis <
[EMAIL PROTECTED]> wrote:

> Hi Everyone,
>
> After spending some time searching through our good friend Mr. Google and
> the mailing list I found a post that provided me with a query that does just
> what I need.
>
> However, the query takes FOREVER and although this is stated in the
> original mail I thought I would ask if there was any advice that you might
> be able to provide to speed things up a little.
>
> And while the query does return over 27,000 rows in my case, I didn't
> expect it to take 9 minutes and 11 seconds!
>
> Please find the query below - and of course - thanks in advance for any
> assistance you might be able to provide me with!
>
> select
>(select
> count(*)
> from
> myTable as myCount
> where
> myCount.contactdate <= myTable.contactdate
>) as rownum,
>contactdate
> from
>myTable
> where
>contactdate > '2007-06-30 23:59:59'
> order by
>contactdate;
>
>  


Posting EXPLAIN command's output would have helped here.

This sub-query in the SELECT list is a correlated sub-query, so it will be
executed for each row that passes the oouter query's WHERE clause. If you
don't have it already, I'd suggest creating an index on the 'contactdate'
column; that should help speed up the query. In absence of such an index,
the planner will choose Sequential Scan, which is very
expensive/time-consuming.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [SQL] Add a ROWCOUNT to the output of a select.

[Robins Tharakan]

While we could always check for the query performance reasons, I rather
think that this is an overkill for the purpose of mere line numbers.

If such queries don't change frequently, you could be better off using a
simple function that instead adds a 'rownumber' field to the output of the
inner SQL query. The 'rownumber' could instead be calculated by simply
incrementing it within a FOR loop for each row.

*Robins*

On Wed, May 14, 2008 at 5:24 AM, Gavin 'Beau' Baumanis <
[EMAIL PROTECTED]> wrote:

> Hi Everyone,
>
> After spending some time searching through our good friend Mr. Google and
> the mailing list I found a post that provided me with a query that does just
> what I need.
>
> However, the query takes FOREVER and although this is stated in the
> original mail I thought I would ask if there was any advice that you might
> be able to provide to speed things up a little.
>
> And while the query does return over 27,000 rows in my case, I didn't
> expect it to take 9 minutes and 11 seconds!
>
> Please find the query below - and of course - thanks in advance for any
> assistance you might be able to provide me with!
>
> select
>(select
> count(*)
> from
> myTable as myCount
> where
> myCount.contactdate <= myTable.contactdate
>) as rownum,
>contactdate
> from
>myTable
> where
>contactdate > '2007-06-30 23:59:59'
> order by
>contactdate;
>
>
> -Gavin
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>