Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

support CH functions for dates and times #131

Open
10 of 51 tasks
Tracked by #130
jinmingjian opened this issue Jun 14, 2021 · 4 comments
Open
10 of 51 tasks
Tracked by #130

support CH functions for dates and times #131

jinmingjian opened this issue Jun 14, 2021 · 4 comments
Assignees
@jinmingjian
Labels
component/engine good first issue help-wanted
Projects
ClickHouse Compatibility

Comments

@jinmingjian
Copy link
Contributor

jinmingjian commented Jun 14, 2021 •
edited

  • toDate
  • toYear
  • toQuarter
  • toMonth
  • toDayOfYear
  • toDayOfMonth
  • toDayOfWeek
  • toHour
  • toMinute
  • toSecond
  • toTimeZone
  • toUnixTimestamp
  • toStartOfYear
  • toStartOfISOYear
  • toStartOfQuarter
  • toStartOfMonth
  • toMonday
  • toStartOfWeek(t[,mode])
  • toStartOfDay
  • toStartOfHour
  • toStartOfMinute
  • toStartOfFiveMinute
  • toStartOfTenMinutes
  • toStartOfFifteenMinutes
  • toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])
  • toTime
  • toRelativeYearNum
  • toRelativeQuarterNum
  • toRelativeMonthNum
  • toRelativeWeekNum
  • toRelativeDayNum
  • toRelativeHourNum
  • toRelativeMinuteNum
  • toRelativeSecondNum
  • toISOYear
  • toISOWeek
  • toWeek(date[,mode])
  • toWeek(date,[, mode][,Timezone])
  • toYearWeek(date[,mode])
  • now
  • today
  • yesterday
  • timeSlot
  • toYYYYMM
  • toYYYYMMDD
  • toYYYYMMDDhhmmss
  • addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters
  • subtractYears, subtractMonths, subtractWeeks, subtractDays, subtractHours, subtractMinutes, sub tractSeconds, subtractQuarters
  • dateDiff('unit', startdate, enddate,[timezone])
  • timeSlots(StartTime, Duration,[, Size])
  • formatDateTime(Time, Format[, Timezone])
@jinmingjian jinmingjian mentioned this issue Jun 14, 2021
Open
3 tasks
@jinmingjian jinmingjian added this to Waiting List in ClickHouse Compatibility Jun 14, 2021
@jinmingjian jinmingjian self-assigned this Jun 22, 2021
This was referenced Jun 24, 2021
Merged
Merged
@frank-king
Copy link
Contributor

frank-king commented Jun 28, 2021 •
edited

Here is my supporting plan based on the Documentation of ClickHouse Dates and Times function.

  1. First the most common date/time converting/parsing/formatting functions with timezone:
  • toDate
  • toTime
  • toDateTime
  • toYear
  • toQuarter
  • toMonth
  • toDayOfYear
  • toDayOfMonth
  • toDayOfWeek
  • toHour
  • toMinute
  • toSecond
  • FROM_UNIXTIME
  • toTimeZone
  • formatDateTime(Time, Format[, Timezone])
  • toUnixTimestamp
  • dateName
  • now
  • today
  • yesterday
  • toYYYYMM
  • toYYYYMMDD
  • toYYYYMMDDhhmmss
  1. Then the date/time truncations:
  • toStartOfYear
  • toStartOfISOYear
  • toStartOfQuarter
  • toStartOfMonth
  • toMonday
  • toStartOfWeek(t[,mode])
  • toStartOfDay
  • toStartOfHour
  • toStartOfMinute
  • toStartOfFiveMinute
  • toStartOfTenMinutes
  • toStartOfFifteenMinutes
  • toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])
  1. Next, the date/time additions/subtractions:
  • addYears
  • addMonths
  • addWeeks
  • addDays
  • addHours
  • addMinutes
  • addSeconds
  • addQuarters
  • subtractYears
  • subtractMonths
  • subtractWeeks
  • subtractDays
  • subtractHours
  • subtractMinutes
  • subtractSeconds
  • subtractQuarters
  • dateDiff('unit', startdate, enddate, [timezone])
  1. Finally, other date/time functions:
  • toRelativeYearNum
  • toRelativeQuarterNum
  • toRelativeMonthNum
  • toRelativeWeekNum
  • toRelativeDayNum
  • toRelativeHourNum
  • toRelativeMinuteNum
  • toRelativeSecondNum
  • toISOYear
  • toISOWeek
  • toWeek(date[,mode])
  • toWeek(date,[, mode][,Timezone])
  • toYearWeek(date[,mode])
  • timeSlots(StartTime, Duration,[, Size])
  • toModifiedJulianDay
  • toModifiedJulianDayOrNull
  • fromModifiedJulianDay
  • fromModifiedJulianDayOrNull

@jinmingjian
Copy link
Contributor Author

@whjpji thanks for detail sketch!
Timezone is important. In fact, all date/time functions in CH are timezone-aware explicitly or implicitly. The timestamp itself is UTC based, but the interpretation to ts is the server's timezone based, at least for CH. This means, a query to same physical data will return different results if the server' tz is different.

If you are interesting to figure out a best tz hooking way for TB to DataFusion before we start more datetime functions? this problem is tracked by issue #27 in fact.

@frank-king
Copy link
Contributor

@jinmingjian Yes I agree with you that timezone is important. Actually I often meet the timezone related issues when doing CRUD.

Let me see how TB and DF handles timezone, and I may adjust the priority of the support plans.

@jinmingjian
Copy link
Contributor Author

@whjpji great, let me know if any problem.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@jinmingjian jinmingjian

Labels
component/engine good first issue help-wanted
Projects
ClickHouse Compatibility
Waiting List
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jinmingjian @frank-king