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.

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

Date to string function #366

Open
max-sixty opened this issue Apr 16, 2022 · 7 comments
Open

Date to string function #366

max-sixty opened this issue Apr 16, 2022 · 7 comments
Labels
language-design Changes to PRQL-the-language major-feature

Comments

@max-sixty
Copy link
Member

In SQL, formatting dates is not easy or elegant; e.g. getting the year from a date, and differs between DBs:

  • Postgres uses TO_CHAR('2018-12-12', 'YYYY-MM')
  • MySQL & BigQuery uses DATE_FORMAT('2018-12-12', '%y-%m')

I quite like the python f-string approach of just f"{date:%y-%m}"

This is (maybe the first) issue where implementing for multiple DBs won't be trivial — I guess we are going to have to choose an approach and then write a mapping to the different date formats — more difficult than just switching out a function.

@max-sixty max-sixty added the language-design Changes to PRQL-the-language label Apr 16, 2022
@max-sixty max-sixty added this to the 0.2 milestone Apr 16, 2022
@aljazerzen
Copy link
Member

I think that this formatting approach is very convenient and is the way to go.

But it's true that is no small feat to implement for different dialects. Because even the format strings are different, we will have to parse them and convert to required dialect.

But a question opens: would we want f"{date:%y-%m}" or f"{date:YYYY-MM}"? Or something else?

@max-sixty
Copy link
Member Author

But a question opens: would we want f"{date:%y-%m}" or f"{date:YYYY-MM}"? Or something else?

I'm not a great fan of the %, but it does seem to be more standard. Go has a different approach that is very readable, but less writable.

That link also has the Java approach, which is not bad at all, but I don't know whether it has broad adoption.

@max-sixty max-sixty modified the milestones: 0.2, 0.3 Jun 26, 2022
@snth snth modified the milestones: 0.3, 0.5 Dec 21, 2022
@snth
Copy link
Member

snth commented Dec 21, 2022

I really like this f-string formatting suggestion and agree that it's the way to go. I've been using the f-strings quite a bit already and was thinking how great it would be if they could also do formatting of dates and numbers.

@vanillajonathan
Copy link
Collaborator

I really like the f-string approach too.

However, maybe it should be easy format the date according to a standard instead of having to specify the format yourself. There are date standards such as ISO 8601, RFC 2822 and RFC 3339.

There could be methods on a date object.
Example:

let string1 = date.to_rfc2822_string()
let string2 = date.to_rfc3339_string()

@snth
Copy link
Member

snth commented Feb 10, 2023

I'm familiar with ISO 8601 and e.g. Python has .isoformat() methods.

I'm not familiar with RFC 2822 and RFC 3339 so I think we would need better names for that. What do these formats look like?

@vanillajonathan
Copy link
Collaborator

Yeah, sure the names could be very different, those were just some examples for how it could be done with methods, but it could also be by done by having a method take in a DateFormat enum:

let string1 = date.to_string(DateFormat.RFC2822)
let string2 = date.to_string(DateFormat.ISO8601)

Or just a string:

let string1 = date.to_string("rfc2822")
let string2 = date.to_string("iso8601")

My point was that it should be easy to format a date according to some standard and this could be done using a method.

@aljazerzen
Copy link
Member

I agree, formatting to ISO8601 (and maybe RFC2822) should be part of the standard library.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
language-design Changes to PRQL-the-language major-feature
Projects
None yet
Development

No branches or pull requests

4 participants