Is it possible to store negative values ​​in timestamps?

[zhangsanhuo]

Proposal:

Let's imagine a scenario where a building was built in 1730. We want to save the construction date of this building and want to arrange these buildings in reverse chronological order, so we choose the timestamp field to store the date. However, in manticore, the date 1730-01-01 is converted to 1016254249, not a negative timestamp, so the correct date cannot be obtained after the conversion.

Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

- [ ] Implementation completed - [ ] Tests developed - [ ] Documentation updated - [ ] Documentation reviewed - [ ] Changelog updated - [x] OpenAPI YAML updated and issue created to rebuild clients

[sanikolaev]

Great explanation! However, using a negative timestamp wouldn't solve the problem in this instance. The calculation for a year like 1730 would be -(1970-1730)*365*86400=-7,568,640,000, which exceeds the signed integer range of -2,147,483,648 to 2,147,483,647.

A better solution would be to support a bigint timestamp format that allows negative timestamps for dates prior to 1970. Additionally, if we implement millisecond resolution, this would enable us to represent a timeline of approximately 2^64/1000/86400/365 = 584,942,417 years.

The related issue is https://github.com/manticoresoftware/manticoresearch/issues/421

[sanikolaev]

As discussed on "dev call of Jul 4 2024", the way date/time functions are implemented now (inc. using library CCTZ) implies using int, not bigint. It's a complex tax to integrate using bigint timestamps to all the date/time functions.

@zhangsanhuo can you please elaborate more on what operations you'd like to be able to do with a timestamp < year 1970 ?

[zhangsanhuo]

As discussed on "dev call of Jul 4 2024", the way date/time functions are implemented now (inc. using library CCTZ) implies using int, not bigint. It's a complex tax to integrate using bigint timestamps to all the date/time functions.

@zhangsanhuo can you please elaborate more on what operations you'd like to be able to do with a timestamp < year 1970 ?

Thank you for your reply. The main purpose is to sort the buildings in reverse order of their completion dates. For example, some users may want to query all aesthetic buildings completed from 1730 to the present and display them chronologically. This will facilitate the analysis of architectural styles from various countries over the years.

[sanikolaev]

users may want to query all aesthetic buildings completed from 1730 to the present and display them chronologically

so it's first filtering, then sorting, no more complex date/time arithmetic (which accounts timezones, leap years etc.). Have you considered using just bigint instead of timestamp?

[zhangsanhuo]

1016254249

Yes, thanks for your reminder, we will try to use bigint field instead of timestamp field.