Question

我下面问:

select a.event_date,
    date_format(date_trunc( month , a.event_date),  %m/%d/%Y ) as date
from monthly_test_table a
order by 1;

产出:

 2017-09-15 | 09/01/2017
 2017-10-01 | 09/30/2017
 2017-11-01 | 11/01/2017

谁能告诉我,“2017-10-01”在使用日期_trunc之后将我的日期显示为“09/30/2017”。

Thanks in Advance...!

Answer 1

You are reverse formatting so it is incorrect. Use the below Code

select a.event_date,
    date_format(date_trunc( month , a.event_date),  %Y/%m/%d ) as date
from monthly_test_table a
order by 1;

Answer 2

您可使用<代码>date_add,其逻辑是将1天(日期)细分为<代码>trunc。

For eg:

2017-10-01 - day( 2017-10-01 ) is 1 and you add 1-1=0 days 
2017-08-30 - day( 2017-08-30 ) is 30 and you add 1-30=-29 days

I faced the same issue recently and resorted to using this logic.

date_add(from_unixtime(unix_timestamp(event_date, yyyy-MM-dd ), yyyy-MM-dd ),
         1-day(from_unixtime(unix_timestamp(event_date, yyyy-MM-dd ), yyyy-MM-dd ))
        )

PS:就一所知,在。参考文件。

Answer 3

根据以下来源代码:UTC_CHRONOLOGY 时间也译为Description。

@Description("truncate to the specified precision in the session timezone")
@ScalarFunction("date_trunc")
@LiteralParameters("x")
@SqlType(StandardTypes.DATE)
public static long truncateDate(ConnectorSession session, @SqlType("varchar(x)") Slice unit, @SqlType(StandardTypes.DATE) long date)
{
    long millis = getDateField(UTC_CHRONOLOGY, unit).roundFloor(DAYS.toMillis(date));
    return MILLISECONDS.toDays(millis);
}

见 :

Time Zones:
This release has full support for time zone rules, which are needed to perform date/time calculations correctly. Typically, the session time zone is used for temporal calculations. This is the time zone of the client computer that submits the query, if available. Otherwise, it is the time zone of the server running the Presto coordinator.

Queries that operate with time zones that follow daylight saving can produce unexpected results. For example, if we run the following query to add 24 hours using in the America/Los Angeles time zone:

www.un.org/Depts/DGACM/index_french.htm

产出:2014-03-09 10:00:00.000

友情链接