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

Re: date_part()/EXTRACT(second) behaviour with time data type

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Gregory Stark <stark(at)mit(dot)edu>
Cc: Postgres <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: date_part()/EXTRACT(second) behaviour with time data type
Date: 2009-07-29 16:15:42
Message-ID: 19958.1248884142@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Gregory Stark <stark(at)mit(dot)edu> writes:
> I think we broke date_part for extracting seconds from time arguments. It
> appears we leave out the milliseconds whereas we don't for timestamp
> arguments. This was not the case in 8.3 where we included the milliseconds for
> both data types.

It's not new.  This appears to be a difference between the integer and
float timestamp code paths, and I'd say it's probably a thinko:

            case DTK_SECOND:
#ifdef HAVE_INT64_TIMESTAMP
                result = tm->tm_sec + fsec / USECS_PER_SEC;
#else
                result = tm->tm_sec + fsec;
#endif
                break;

In the integer case, fsec is an integer and so the division loses the
fraction.  timestamptz_part does this instead:

            case DTK_SECOND:
#ifdef HAVE_INT64_TIMESTAMP
                result = tm->tm_sec + fsec / 1000000.0;
#else
                result = tm->tm_sec + fsec;
#endif
                break;

I agree that we should change it, but should we back-patch it, and if so
how far?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Euler Taveira de OliveiraDate: 2009-07-29 16:23:22
Subject: Re: WIP: to_char, support for EEEE format
Previous:From: Andrew DunstanDate: 2009-07-29 15:53:02
Subject: Re: xpath not a good replacement for xpath_string

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