[functions] Functions Working with Date/DateTime

[sundy-li]

From https://clickhouse.tech/docs/en/sql-reference/functions/date-time-functions/

prio order from high to low

• now (Logical types #1648)
• today (Logical types #1648)
• yesterday (Add yesterday, tomorrow date functions #1673)
• tomorrow (Add yesterday, tomorrow date functions #1673)
• toDate, toDateTime (cast number/string into Date/DateTime)
• timeSlot (Add timeSlot() round functions #1715)
• toYYYYMM (Add toYYYYMM function #1726)
• toYYYYMMDD (Add toYYYYMMDDhhmmss function #1767)
• toYYYYMMDDhhmmss (Add toYYYYMMDDhhmmss function #1767)
• toStartOfYear (add toStartOfYear,toStartOfISOYear #1826)
• toStartOfISOYear (add toStartOfYear,toStartOfISOYear #1826)
• toStartOfQuarter add toStartOfQuarter,toStartOfMonth #1848
• toStartOfMonth add toStartOfQuarter,toStartOfMonth #1848
• toStartOfWeek (add toStartOfWeek #1942)
• toStartOfDay (Add timeSlot() round functions #1715)
• toStartOfHour (Add timeSlot() round functions #1715)
• toStartOfMinute (Add timeSlot() round functions #1715)
• toStartOfSecond (Add timeSlot() round functions #1715)
• toStartOfFiveMinute (Add timeSlot() round functions #1715)
• toStartOfTenMinutes (Add timeSlot() round functions #1715)
• toStartOfFifteenMinutes (Add timeSlot() round functions #1715)
• toMonth ([Feature]Add toMonth date function #2042)
• toDayOfYear ([Feature]Add toDayOfYear/toDayOfMonth/toDayOfWeek #2050)
• toDayOfMonth ([Feature]Add toDayOfYear/toDayOfMonth/toDayOfWeek #2050)
• toDayOfWeek ([Feature]Add toDayOfYear/toDayOfMonth/toDayOfWeek #2050)
• toHour ([Feature]Add toHour/toMinute/ToSecond #2309)
• toMinute ([Feature]Add toHour/toMinute/ToSecond #2309)
• toSecond ([Feature]Add toHour/toMinute/ToSecond #2309)
• toMonday (Add toMonday function #2317)

Low priority:

• timeZone
• toTimeZone
• timeZoneOf
• timeZoneOffset
• toStartOfInterval
• toYear
• toQuarter
• toUnixTimestamp
• toTime
• toRelativeYearNum
• toRelativeQuarterNum
• toRelativeMonthNum
• toRelativeWeekNum
• toRelativeDayNum
• toRelativeHourNum
• toRelativeMinuteNum
• toRelativeSecondNum
• toISOYear
• toISOWeek
• toWeek
• toYearWeek
• date_trunc
• date_add
• date_diff
• date_sub
• timestamp_add
• timestamp_sub
• addYears
• subtractYears
• timeSlots
• formatDateTime
• dateName
• FROM_UNIXTIME
• toModifiedJulianDay
• toModifiedJulianDayOrNull
• fromModifiedJulianDay
• fromModifiedJulianDayOrNull

[gauss1314]

Let me implement function: toStartOfYear.

[dust1]

Hello i am interested.
Does this mean I need to implement the above functions in the SELECT statement like the clickhouse example : SELECT toDateTime('2016-06-15 23:00:00') AS time, toDate(time) AS date_local, toDate(time, 'Asia/Yekaterinburg') AS date_yekat, toString(time, 'US/Samoa') AS time_samoa ?

[sundy-li]

Does this mean I need to implement the above functions in the SELECT statement like the clickhouse example

Yes, but we must introduce Date/DateTime datatypes in previous

[dust1]

Yes, but we must introduce Date/DateTime datatypes in previous

A bit challenging. Maybe I can try it.
Can you give me some reference about how to introduce Date/DateTime datatypes ?
And it's a task lists, Can I submit a PR of a certain function separately ?

[sundy-li]

Can you give me some reference about how to introduce Date/DateTime datatypes ?

Since Datafuse runtime compute system is base on arrow2, so we are considering help to add custom logic datatypes in arrow2. Refer: jorgecarleitao/arrow2#326

[dust1]

/help

[databend-bot]

/assignme -- assign the issue to you
/help -- show help

[dust1]

/assignme

[sundy-li]

@gauss1314 @dust1

Now we have Date16(clickhouse's Date), Date32, DateTime32 types, and we have an example function now, today in https://github.com/datafuselabs/datafuse/tree/master/common/functions/src/scalars/dates

You can continue this task now. Let's implement it in order by priority.

[dust1]

You can continue this task now. Let's implement it in order by priority.

ok

[ygf11]

@sundy-li I am also interested in these task.
To avoid conflict, which one should I take?

[sundy-li]

@sundy-li I am also interested in these task.
To avoid conflict, which one should I task?

Thanks, You can try these three first

 toYYYYMM
 toYYYYMMDD
 toYYYYMMDDhhmmss

Since they are all return numbers, so better to design it as a generic function or something like LogicFunction

[ygf11]

Since they are all return numbers, so better to design it as a generic function or something like LogicFunction

ok.

[TCeason]

/assign

Could I try to impl the func timezone?

[sundy-li]

Could I try to impl the func timezone?

Yes, but we better add timezone into DateTime Type in previous, you can try this task #1680

[taiyang-li]

/assignme

Could I try toDate and toDateTime ? @sundy-li

[GrapeBaBa]

@sundy-li cool, which one can i try?

[sundy-li]

@sundy-li cool, which one can i try?

You can try from toMonth --> toMonday

[GrapeBaBa]

@sundy-li cool, which one can i try?

You can try from toMonth --> toMonday

@sundy-li I saw this items with WIP label, Is somebody doing these?

[sundy-li]

@sundy-li cool, which one can i try?

You can try from toMonth --> toMonday

@sundy-li I saw this items with WIP label, Is somebody doing these?

I set it to wip means you are assigned of this, I'll update the assigners

[dust1]

Next, do I implement the oStartOfYear and toStartOfISOYear functions or other?

[dust1]

toStartOfQuarter
toStartOfMonth
toStartOfWeek

[dust1]

The toStartOfInterval function only needs to be implemented INTERVAL x year, INTERVAL x month, INTERVAL x day, INTERVAL x minute ,INTERVAL x house`,Will there be others?

[Xuanwo]

Is this issue still going on?

[sundy-li]

Let's close it, thanks for all the contributors.