Re: Oddity with extract microseconds?

Does anyone else find this odd:

mysql=# select extract(microseconds from timestamp '2005-01-01
00:00:00.123');
date_part
-----------
123000
(1 row)

mysql=# select extract(microseconds from timestamp '2005-01-01
00:00:01.123');
date_part
-----------
1123000
(1 row)

No other extracts include other fields. eg, minutes:

mysql=# select extract(minutes from timestamp '2005-01-01 00:10:00');
date_part
-----------
10
(1 row)

mysql=# select extract(minutes from timestamp '2005-01-01 10:10:00');
date_part
-----------
10

So how come microseconds includes the microseconds from the 'seconds'
field and not just after the '.'? And if it's supposed to include
'seconds', then why doesn't it include minutes, hours, etc.?

Chris

OK, AndrewSN just pointed out that it's "documented" to work like that...

...still seems bizarre...

Chris

Christopher Kings-Lynne wrote:
> Does anyone else find this odd:
>
> mysql=# select extract(microseconds from timestamp '2005-01-01
> 00:00:00.123');
> date_part
> -----------
> 123000
> (1 row)
>
> mysql=# select extract(microseconds from timestamp '2005-01-01
> 00:00:01.123');
> date_part
> -----------
> 1123000
> (1 row)
>
> No other extracts include other fields. eg, minutes:
>
> mysql=# select extract(minutes from timestamp '2005-01-01 00:10:00');
> date_part
> -----------
> 10
> (1 row)
>
> mysql=# select extract(minutes from timestamp '2005-01-01 10:10:00');
> date_part
> -----------
> 10
>
> So how come microseconds includes the microseconds from the 'seconds'
> field and not just after the '.'? And if it's supposed to include
> 'seconds', then why doesn't it include minutes, hours, etc.?
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Christopher Kings-Lynne wrote:
> OK, AndrewSN just pointed out that it's "documented" to work like that...
>
> ...still seems bizarre...

So it's a "gotcha"!

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> OK, AndrewSN just pointed out that it's "documented" to work like that...
> ...still seems bizarre...

It seems reasonably consistent to me. extract() doesn't consider
seconds and fractional seconds to be distinct fields: it's all one
value. The milliseconds and microseconds options just shift the
decimal place for you.

regards, tom lane

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> > OK, AndrewSN just pointed out that it's "documented" to work like that...
> > ...still seems bizarre...
>
> It seems reasonably consistent to me. extract() doesn't consider
> seconds and fractional seconds to be distinct fields: it's all one
> value. The milliseconds and microseconds options just shift the
> decimal place for you.

I think this illustrates the issue:

test=> SELECT date_part('microseconds', '00:00:01.33'::time);
date_part
-----------
1330000
(1 row)

test=> SELECT date_part('microseconds', '00:03:01.33'::time);
date_part
-----------
1330000
(1 row)

Why aren't 'minutes' considered too? Because they aren't 'seconds'.
Well, seconds aren't microseconds either.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Why aren't 'minutes' considered too? Because they aren't 'seconds'.
> Well, seconds aren't microseconds either.

Yeah, they are: it's just one field. The other way of looking at it
(that everything is seconds) is served by "extract(epoch)".

regards, tom lane

>>Why aren't 'minutes' considered too? Because they aren't 'seconds'.
>>Well, seconds aren't microseconds either.
>
> Yeah, they are: it's just one field. The other way of looking at it
> (that everything is seconds) is served by "extract(epoch)".

Well, it's different in MySQL unfortunately - what does the standard
say? Out of interest, can someone try this for me in MySQL 5:

SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123');
SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:10.00123');

Chris

On Wed, Dec 07, 2005 at 09:43:30AM +0800, Christopher Kings-Lynne wrote:
> >>Why aren't 'minutes' considered too? Because they aren't 'seconds'.
> >>Well, seconds aren't microseconds either.
> >
> >Yeah, they are: it's just one field. The other way of looking at it
> >(that everything is seconds) is served by "extract(epoch)".
>
> Well, it's different in MySQL unfortunately - what does the standard
> say?

I don't see microseconds as a possible field in SQL:2003 (draft copy).

> Out of interest, can someone try this for me in MySQL 5:
>
> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123');
> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:10.00123');

MySQL 5.0.16 gives an error:

mysql> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'FROM '2003-01-02 10:30:00.00123')' at line 1

--
Michael Fuhr

> MySQL 5.0.16 gives an error:
>
> mysql> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123');
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'FROM '2003-01-02 10:30:00.00123')' at line 1

Odd, that example is straight from the MySQL 5 manual:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Chris

On Wed, Dec 07, 2005 at 10:32:20AM +0800, Christopher Kings-Lynne wrote:
> >MySQL 5.0.16 gives an error:
> >
> >mysql> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123');
> >ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> >that corresponds to your MySQL server version for the right syntax to use
> >near 'FROM '2003-01-02 10:30:00.00123')' at line 1
>
> Odd, that example is straight from the MySQL 5 manual:
>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Looks like MySQL doesn't allow a space before the open parenthesis
(there isn't one in the manual's example):

mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123');
+-------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123') |
+-------------------------------------------------------+
| 1230 |
+-------------------------------------------------------+
1 row in set (0.01 sec)

--
Michael Fuhr

> Looks like MySQL doesn't allow a space before the open parenthesis
> (there isn't one in the manual's example):
>
> mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123');
> +-------------------------------------------------------+
> | EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123') |
> +-------------------------------------------------------+
> | 1230 |
> +-------------------------------------------------------+
> 1 row in set (0.01 sec)

Ok, and what does this give:

SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123');

Chris

On Wed, Dec 07, 2005 at 10:47:45AM +0800, Christopher Kings-Lynne wrote:
> Ok, and what does this give:
>
> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123');

mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123');
+-------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123') |
+-------------------------------------------------------+
| 1230 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

Does contrary behavior from MySQL count as evidence that PostgreSQL's
behavior is correct? :-)

--
Michael Fuhr

> mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123');
> +-------------------------------------------------------+
> | EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123') |
> +-------------------------------------------------------+
> | 1230 |
> +-------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> Does contrary behavior from MySQL count as evidence that PostgreSQL's
> behavior is correct? :-)

No...I happen to think that their way is more consistent though. Pity
it's not in the spec.

At least PostgreSQL is consistent with seconds/microseconds:

mysql=# select extract(seconds from timestamp '2005-01-01 00:00:01.01');
date_part
-----------
1.01
(1 row)

Chris

In article <439650F1(dot)4050901(at)familyhealth(dot)com(dot)au>,
Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:

mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123');
>> +-------------------------------------------------------+
>> | EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123') |
>> +-------------------------------------------------------+
>> | 1230 |
>> +-------------------------------------------------------+
>> 1 row in set (0.00 sec)
>> Does contrary behavior from MySQL count as evidence that PostgreSQL's
>> behavior is correct? :-)

> No...I happen to think that their way is more consistent though. Pity
> it's not in the spec.

I'd say the comparison with MySQL is useless because MySQL is unable
to store microseconds in a DATETIME or TIMESTAMP column, although you
can extract microseconds from a date/time literal.

Christopher Kings-Lynne wrote:

>>> Why aren't 'minutes' considered too? Because they aren't 'seconds'.
>>> Well, seconds aren't microseconds either.
>>
>>
>> Yeah, they are: it's just one field. The other way of looking at it
>> (that everything is seconds) is served by "extract(epoch)".
>
>
> Well, it's different in MySQL unfortunately - what does the standard
> say? Out of interest, can someone try this for me in MySQL 5:
>
> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123');
> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:10.00123');

mysql 4.1.5 gives back 123 in both cases. I assume they haven't changed
that, although anything is possible.

cheers

andrew