Timestamp with timezone

@all Currently the timestamps

• ope_date_debut ope_date_finin table t_operation_ope
• per_date_debut per_date_fin from t_periodefonctdispositif_per
• env_date_debut env_date_fin from tj_conditionenvironnementale_env
• txe_date_debut txe_date_fin from tj_tauxechappement_txe are timestamp without time zone.

When loaded into R by stacomiR they are condidered as UTC.

bMM <- new("report_mig_mult")
bMM <- choice_c(bMM,
    dc=c(5),
    taxa=c("Alosa"),
    stage=c("11"),
    datedebut=str_c(CY,"-01-01"),
    datefin=str_c(CY,"-12-31"))
bMM <- charge(bMM)
bMM <- connect(bMM)
bMM <- calcule(bMM)

tz(bMM_Arzal@data$time)

[1] "UTC" Message d'avis : tz(): Don't know how to compute timezone for object of class NULL; returning "UTC".

Most of the time we don't have to worry about it but let's say we want to use the character string to join with another table

```r
toto <- data.frame(ope_date_debut = as.POSIXct(strptime("2002-08-01 12:00:00", format = "%Y-%m-%d %H:%M:%S"), tz="UTC"))
 tz(toto$ope_date_debut)
# [1] "UTC"
#there is not problem untill we do
strftime(toto$ope_date_debut, "Europe/Paris",format="%Y-%m-%d %H:%M:%S")
"2002-08-01 14:00:00" 

😮 Astonishment, we are dumbfounded. Now there is a shift of two hours, creepy, nasty, rarerly spotted it but it's here, because your locale is CEST. And be sure the data coming from the db will be UTC.

> Sys.timezone()
[1] "Europe/Paris"

#  
#We could do
strftime(toto$ope_date_debut, "Europe/Paris",format="%Y-%m-%d %H:%M:%S", tz="UTC", usetz =TRUE)
"2002-08-01 12:00:00 UTC" 
# But then we have a nasty UTC at the end of the string.
# Now to fix this one solution is :
fixme <- function(data, nom_coldt){
data[,{{nom_coldt}}] <- lubridate::force_tz(data[,{{nom_coldt}}], "Europe/Paris")
}
toto$ope_date_debut <- fixme(toto, "ope_date_debut")
strftime(toto$ope_date_debut, "Europe/Paris",format="%Y-%m-%d %H:%M:%S")
# [1] "2002-08-01 12:00:00"

But the solution is to use time zones in the database and it's really easy

ALTER TABLE your_table
ALTER COLUMN your_column TYPE timestamptz
USING your_column AT TIME ZONE 'Europe/Paris';