prql
prql copied to clipboard
Date to string function
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.
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?
But a question opens: would we want
f"{date:%y-%m}"
orf"{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.
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.
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()
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?
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.
I agree, formatting to ISO8601 (and maybe RFC2822) should be part of the standard library.