4

I have data which has two columns time and timezone that have the timestamp of events. Examples are:

time               timezone
1433848856453      10800000

It seems like the timestamp has fractional seconds in the information as well. I don't understand the timezone format but it must be an equivalent unix format. I need to preserve the fractional seconds as well. How do I go from that to something like in R?

2015-01-01 13:34:56.45 UTC

Note: This human readable date is not the actual converted values of the unix timestamp shown.

scoa
  • 19,359
  • 5
  • 65
  • 80
sfactor
  • 12,592
  • 32
  • 102
  • 152
  • `timezone` column has also negative values?. Anyway I think Joshua had a very good idea about how to interpret `timezone`; milliseconds + or - the hour due to time zones. – SabDeM Jun 25 '15 at 14:18

2 Answers2

5

It looks like the timezone column is the timezone offset, in milliseconds. I assume that means the timezone column will adjust for daylight saving time manually

So you should add the time and timezone columns before converting to POSIXct. You should also set the tz to "UTC" so no DST adjustments will be made to your POSIXct object.

R> time <- 1433848856453
R> timezone <- 10800000
R> options(digits.secs=3)
R> .POSIXct((time+timezone)/1000, tz="UTC")
[1] "2015-06-09 14:20:56.453 UTC"
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • I'm no expert in R but i'm a bit puzzled, why does the timezone have to go in... The Unix timestamp is already enough information to define a moment. Is there something about these R functions that can't take into account DST adjustments? – Sandman Jun 25 '15 at 16:19
  • @Teo: The problem is that the timezone offset doesn't define a timezone, so there's no way for R to know when DST adjustments occur (there are several timezones that are UTC+3 and they may or may not have different DST rules). That's why I said I assume the timezone column will reflect DST adjustments. – Joshua Ulrich Jun 25 '15 at 18:00
  • The timezone offset may not define a timezone but there should be a timezone defined in the locale/time settings of the machine. For example Javascript knows about DST which may or may not occur in the local timezone (as far as i know & tested). So getting a Date object in Javascript will account for DST even if it's 5 years ago and should convert to UTC accordingly.. I was just curious if R is capable of this too. What's not so nice IMO is if you really need to account for specific timezones (explicitly) to convert a Unix timestamp to a UTC time :) - i assume UTC was never affected by DST – Sandman Jun 25 '15 at 18:26
  • @Teo: Yes, there is (usually) a timezone defined by the OS, and R tries to use that. I adjusted the Unix timestamp for the timezone offset and set the POSIXct object's timezone to UTC so it would represent the local time where it was measured, rather than *my* local time when it was measured at UTC+3. You could do `.POSIXct(time)` to get the Unix time in your local time, but I'm not sure which the OP would prefer. – Joshua Ulrich Jun 25 '15 at 18:46
  • Ok, so i guess you're saying UTC is actually not the timezone of the displayed time and it's UTC+[the given offset] instead. If that's the case i understand.. though maybe it's a bit misleading to not also show "+offset" in the answer. Thanks for the explanations! – Sandman Jun 25 '15 at 19:34
3

I think this can be right for you. 

aa <- 1433848856453
as.POSIXct(aa/1000, origin="1970-01-01")
[1] "2015-06-09 13:20:56.453 CEST"

Edit

Now I've realized that my code is not reproducible, because of my personal settings, but this can fix it and let you achieve the goal.

options(digits.secs=6)
SabDeM
  • 7,050
  • 2
  • 25
  • 38