[GitHub] [arrow-datafusion] waitingkuo commented on pull request #4385: `date_part` support fractions of second

[GitBox <gi...@apache.org>]

waitingkuo commented on PR #4385:
URL: https://github.com/apache/arrow-datafusion/pull/4385#issuecomment-1329835485

   hi @comphead  thank you
   
   i tested the pr
   ```bash
   ❯ select date_part('second', timestamp '2000-01-01T00:00:09.123456');
   +-------------------------------------------------------------+
   | datepart(Utf8("second"),Utf8("2000-01-01T00:00:09.123456")) |
   +-------------------------------------------------------------+
   | 9                                                           |
   +-------------------------------------------------------------+
   1 row in set. Query took 0.002 seconds.
   ❯ select date_part('millisecond', timestamp '2000-01-01T00:00:09.123456');
   +------------------------------------------------------------------+
   | datepart(Utf8("millisecond"),Utf8("2000-01-01T00:00:09.123456")) |
   +------------------------------------------------------------------+
   | 123                                                              |
   +------------------------------------------------------------------+
   1 row in set. Query took 0.002 seconds.
   ❯ select date_part('microsecond', timestamp '2000-01-01T00:00:09.123456');
   +------------------------------------------------------------------+
   | datepart(Utf8("microsecond"),Utf8("2000-01-01T00:00:09.123456")) |
   +------------------------------------------------------------------+
   | 123456                                                           |
   +------------------------------------------------------------------+
   1 row in set. Query took 0.002 seconds.
   ```
   
   the behavior is different than what postgresql has
   ```bash
   willy=# select date_part('second', timestamp '2000-01-01T00:00:09.123456');
    date_part 
   -----------
     9.123456
   (1 row)
   
   willy=# select date_part('millisecond', timestamp '2000-01-01T00:00:09.123456');
    date_part 
   -----------
     9123.456
   (1 row)
   
   willy=# select date_part('microsecond', timestamp '2000-01-01T00:00:09.123456');
    date_part 
   -----------
      9123456
   (1 row)
   ```
   
   i checked some other system,
   spark seems to have the same behavior as postgresql
   ```bash
   # this is spark
   SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001');
   +----------------------------------------------------------+
   |date_part(SECONDS, TIMESTAMP '2019-10-01 00:00:01.000001')|
   +----------------------------------------------------------+
   |                                                  1.000001|
   +----------------------------------------------------------+
   ```
   
   while mysql's is similar as this pr
   ```bash
   # this is MYSQL
   EXTRACT(SECOND FROM "2017-06-20 00:00:01.123456");
   1
   ```
   
   I originally purposed to output f64 instead of i32 since i'd like to follow postgresql's
   
   @alamb  @tustvold  do you have any suggestion?
   
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org