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:
Show quoted text
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
-----------
10So 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@familyhealth.com.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@familyhealth.com.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.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 1Odd, 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.4050901@familyhealth.com.au>,
Christopher Kings-Lynne <chriskl@familyhealth.com.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