Need to explain difference in dates when inserting rows

[jimtyhurst]

Is your feature request related to a problem? Please describe. In Ch. 13 "SQL joins and complex queries":

• Data inserted with dbExecute and SQL code has date fields initialized with now() or now()::date, which is executed within the PostgreSQL server, which seems to use UTC time.
• Data inserted with dbWriteTable and a data.frame initialized with R code Sys.date() or Sys.time() has the local time from my workstation.

This leads to confusion as one works through the chapter, because the row inserted first with dbExecute has a later timestamp than the record inserted second with dbWriteTable.

Describe the solution you'd like Ch. 13 should be enhanced to explain this issue, which is a common problem for web application developers. In general, timestamps should be supplied by the server, not by clients. This can be done by configuring the database with triggers or default values for fields.

Describe alternatives you've considered We could just ignore the issue and address it during the workshop if anybody notices. This is not a specific problem for using R and SQL. It just happens to be highlighted in this chapter with the two ways of inserting records.