remove milliseconds from Datetime in TZ format

Question

I have a datetime column that contains DateTime in TZ format like:

timestamp = 2021-04-01T04:37:16.528Z

I have to convert/truncate the milliseconds part such that datetime is:

as.POSIXct(timestamp, format="%Y-%m-%dT%H:%M:%OSZ", tz="GMT")

2021-04-01 04:37:16

But when I group by DateTime column for aggregations it still uses the timestamp in milliseconds format and the aggregations are not gettig implemented properly.

I want to truncate not just print the DateTime till seconds only.

Answer 1

You could drop the miliseconds part from the string, and then convert:

as.POSIXct(gsub("\\.[0-9]+Z", "", timestamp), 
           format="%Y-%m-%dT%H:%M:%S", tz="GMT")