-
Notifications
You must be signed in to change notification settings - Fork 480
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
FR: Add timestamp formatting function #3594
Comments
Im not sure I understand the problem with nanosecond -> microsecond conversion? isnt it just multiplying by 1000000 |
Hopefully this example will help. Say you have a timestamps like this They could originate in the same log or could come from separate sources which need to be post-processed to a consistent format so that you can send them to an API, as strings, which requires them to be formatted consistently like this Parsing them with timestamp() means the Nanosecond component will be int 0 and 123456000 respectively. Multiplication can help with the second one but not the first because it won't pad it out to become "000". Truncating them, as I did in the above example using regex, will also work for the second timestamp but not the first. Being able to specify a string format should do the necessary padding of the 1st timestamp and also the trimming of the 2nd timestamp. |
integers or floats are always formatted according the format string directive in format() . The example you gave with 0 milliseconds can be formatted with 0 pad both before and after the decimal dot as required. For example to pad to 2 digits before the dot and 3 digits after: LET T <= timestamp(epoch="2024-02-02T04:42:00Z")
SELECT format(format="%d!%02d-%02dT%02d:%02d:%06.3fZ", args=[
T.Year, T.Month, T.Day, T.Hour, T.Minute, T.Nanosecond / 1000000000.0
]) FROM scope() Gives |
Thanks that's good to know, and I didn't know that which highlights the point I'm making about such an approach being messy. I don't think we should expect users to be proficient with that formatting syntax. The time.Time layouts are much more comprehensible: LET T <= timestamp(epoch="2024-02-02T04:42:00Z")
LET L <= "2006-01-02T15:04:05.000"
SELECT format_time(layout=L, time=T) FROM scope() -- or something like that would be awesome Maybe it could even be extra functionality within timestamp() or format() rather than a separate function? |
For anyone dealing with milliseconds/microseconds/nanoseconds in future, this is what's currently needed: LET T <= timestamp(epoch="2024-02-02T04:42:05.26Z")
SELECT format(format="%d!%02d-%02dT%02d:%02d:%02d.%03d", args=[
T.Year, T.Month, T.Day, T.Hour, T.Minute, T.Second, int(int=T.Nanosecond / 1000000)
]) FROM scope() because division always returns a float (surprisingly!), so this applies to T.Nanosecond (an int) divided by anything too. edit: Alternatively you can use this unintuitive LET T <= timestamp(epoch="2024-02-02T04:42:05.26Z")
SELECT format(format="%d!%02d-%02dT%02d:%02d:%02d.%03.f", args=[
T.Year, T.Month, T.Day, T.Hour, T.Minute, T.Second, T.Nanosecond / 1000000
]) FROM scope() |
I don't necessary agree that the timestamp formatting system in the time package is any more readable than the standard formatting directives. It's certainly not as powerful in the type of formatting it can provide. Perhaps we just need a bunch of vql functions to export things into standard formats to emulate the formatting constants in the time package? I'm actually also not sure that in the real world this comes up too frequently as most modern systems should be using iso format. We also don't want to encourage people to actually format their times because we want to keep times in iso format as much as possible. If we were to expose a set of common functions what would be a reasonable useful set? This helps to define a need to see if this needs to be flexible enough |
This is definitely a usability feature rather than a technical limitation because string formatting is very powerful, as you've pointed out. But for most people, including me, this means spending some time looking for examples on stackoverflow. I think there are at least a few popular REST APIs that insist on milliseconds or microseconds. In my case the former, when dealing with IRIS. Even though it's basically accepting ISO format it expects the milliseconds to be explicitly 3 digits. So for example these won't be accepted by the API even though they are valid ISO-8601/RFC-3339:
So maybe we can get by with formatting constants for milli/micro/nano variants for RFC-3339? I see that the time package already has a RFC3339Nano layout constant which I could have used with a simple regex to extract up to the millisecond. Possibly some others that might get used in printed reports:
|
The argument in favour of supporting timestamp "layout" formatting is that if you google "golang format timestamp" most of the results, like this one, will show examples/tutorials of it being done that way, i.e. only ancient examples using string formatting. We also use it for parsing unusual timestamp stings so it's consistent to also use it for output formatting. I don't know if it adds a lot of complexity or bloat. Maybe it does. I hoped it would be something we could get relatively "for free" from the time package. |
I dont think we should be supporting or encouraging any of the timestamp formats you mentioned in your comments (especially not the US style ones). We should only support RFC-3339 timestamps out of the box and make it harder to produce other timestamps. I think this issue is actually highlighting a different problem - You stated that the system you use to consume the RFC 3339 timestamps requires exactly 3 significant decimal digits. This is not a requirement specified in the RFC as far as I can see so it is likely that that system actually does not support RFC 3339 timestamps correctly and we should file a bug against that upstream. I would be very surprised if the upstream system does not accept properly formatted RFC 3339 timestamps which may or may not include fractional second - likely if they use a standard library to parse the times it should work out of the box. Even if we allowed to use RFC3339Nano to format the times you still need to massage them with regex to comply with this weird API requirement so this is a hack at best. To me this requirement is very weird - it is like saying that your API will only accept numbers with exactly 3 digits of precision no more and not less (e.g. 1.000 , 2.000 etc ) - I just cant think of a valid reason to add this restriction |
Yes it is weird, but I confirmed it experimentally and it matches up with their API examples. I didn't look into their code to find the reason and they don't seem to document their timestamp requirements anywhere. The Iris timeline artifact now works robustly (previously it would not work with any artifact result sets having timestamps without fractional seconds) and my interest ends there. In the past I've come across other systems with similarly weird timestamp requirements but we could handle those too with string formatting. The other use cases are not related to data-interchange but rather for human consumption. For example someone may want to write out times to a markdown table for a report. It could be that some Americans want to see US time format in the GUI. Maybe the US military uses Velociraptor and would like to see their weird US military formats. They are not my use cases but they'd seem reasonable if they were to come up. It doesn't come up often (at least that we are aware of) but there are examples on Discord here and here which show that working with timestamp components and formatting is not as easy as it could be. It looks to me like this is the exact reason why the Go layout formatting exists: It spares the user from having to understand timestamp components (as per 2nd example) and advanced string formatting (as per 1st example). The format constants are a nice-to-have bonus but not essential. As I mentioned it's about usability, not capability. A convenience feature. Maybe the demand for it is low, maybe the implementation cost is high - I really don't know. I don't think that having it would encourage people to go wild with timestamp formats. |
There are two ways this can be solved :
I think these solve different problems - the first applies automatically to all timestamps when the second is just for a specific timestamp. I am not sure if IRIS has other timestamps that need massaging as well (I still think it is worth filing an issue against that project if they dont properly support RFC 3339 timestamps). |
Added in #3716 🥳 For people reading this in future, note that you have to subvert the GUI's automatic formatting if you want to see certain formats that are auto-detected and replaced by RFC3339, for example RFC3339Nano: LET mytime <= timestamp(string="2024-03-02T15:04:05.999999999+07:00")
SELECT timestamp_format(time=mytime, format="RFC3339Nano"),
regex_replace(
source=timestamp_format(time=mytime, format="RFC3339Nano"),
replace="!",
re="T")
FROM scope() But usually the point of formatting is that you want to write it out to a file or external service so how the GUI displays it is irrelevant. |
It would be nice to have access to the predefined formats and custom output formatting that Go's time.Format provides (https://pkg.go.dev/time#pkg-constants).
We can currently build custom formats using format() but it can get messy. For example if you need a timestamp string to pass to an external system that demands milliseconds then it requires some string manipulation because we only have nanoseconds available. But if the original parsed timestamp had no fractional seconds then
.Nanosecond
will beNull(actually int 0) which breaks the formatting. This then requires messy VQL to compensate like this:If we could specify something like
Timestamp.Format.StampMilli
that would be more elegant.The text was updated successfully, but these errors were encountered: