PRQL / prql

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement
https://prql-lang.org
Apache License 2.0
9.92k stars 217 forks source link

Temporal types #2125

Open aljazerzen opened 1 year ago

aljazerzen commented 1 year ago

This is continuation of discussion about type system RFC https://github.com/PRQL/prql/pull/1964#discussion_r1119803344

I've taken a look around, and I suggest PRQL to have following data types:

Additionally, we may need special type for time zones and offsets.

(PS: I don't think this is a priority right now, just filing the issue)

vanillajonathan commented 1 year ago

Why are instant or moment needed as separate data types instead of just being functions that create instances of the datetime data type?

I like date, time and datetime.

In .NET date is called DateOnly and time is called TimeOnly.

I think datetime_tz is what is called DateTimeOffset in .NET. Not sure which is the better name.

Are these primitive data types of complex data types (i.e. structs). Structs could have methods and properties on them. Example: datetime.hour or time.minute.

If these are complex data types, maybe they should be named pascal case as opposed to primitive data types which are lower case. Example Date, Time, and DateTime.

PHP have the DateTime class. Python have the datetime class. .NET also have the third-party NodaTime library.

I think it should be possible to add and subtract time. In .NET you can do DateTime.Now.AddHours(3).

max-sixty commented 10 months ago

FYI @aljazerzen and I discussed this on the dev call, we agreed it was potentially cool but required a bunch of infra (e.g. Enums) that we don't have yet. So we can push to the future.

snth commented 10 months ago

I agree with most of these and just have two questions I think:

  1. Like @vanillajonathan asked, what's the difference between instant / moment and datetime?
  2. duration vs period: my understanding from the explanation given is that duration would be a fixed number of seconds whereas period is a "human language" time period like a "month" say, correct? In SQL the term INTERVAL is often used and that could map onto either of these I guess?
aljazerzen commented 10 months ago
  1. instant / moment describes a moment in time. datetime describes a date + time, for example 2031-12-19 08:27, which is time-zone dependent and might describe a different instant if the daylight-saving-time changes in the next 8 years.
  2. Correct, I updated the initial comment
vanillajonathan commented 10 months ago

So what is the difference between instant / moment and datetime_tz?

aljazerzen commented 10 months ago

datetime_tz describes date, time and timezone and depends on the definition of timezone, which might change in the future, due to day-light-saving time. This should be used for setting for example a meeting time, where you want it to be at "9:00 AM CEST on some date", regardless of the changes to the timezone shifts. This type also supports things like "add one month", while instant does not.