使用RPostgreSQL的dbGetQuery导入数据时丢失Datetime时间组件

Hey there! Let's dig into why your datetime values are losing the time component when pulling data from PostgreSQL into R. I've run into similar issues before, so here are a few steps to diagnose and fix this:

1. First, confirm the data type of your datetime column in PostgreSQL #

It's possible the column itself is only storing dates (not timestamps) without you realizing it. To check, run this query directly in PostgreSQL (via pgAdmin, psql, or your preferred tool):

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'chartevents' 
AND column_name = 'datetime';

• If the data_type returns date, that's the problem—this column only stores date values, so the time part was never saved. You'll need to verify if the original data had time components, or if the column was misconfigured.
• If it returns timestamp or timestamptz (timestamp with time zone), the time data is there, and we just need to fix how R is reading it.

2. Check how R is interpreting the column #

In R, run this to see the data type of your datetime column:

str(bp_chartevents_itemid$datetime)

• If the output says Date, that means dbGetQuery automatically converted the PostgreSQL timestamp to a date-only type. To get the time back, convert it to a POSIXct type:

  bp_chartevents_itemid$datetime <- as.POSIXct(bp_chartevents_itemid$datetime, origin = "1970-01-01")
  

• Alternatively, use dbSendQuery + dbFetch instead of dbGetQuery—sometimes this preserves the original timestamp type better:

  res <- dbSendQuery(con, "SELECT DISTINCT itemid, datetime FROM chartevents WHERE valueuom = 'mmHg';")
  bp_chartevents_itemid <- dbFetch(res)
  dbClearResult(res)
  

3. Check if it's just a display issue #

Sometimes R's data frame viewer truncates datetime values to show only the date, even if the time is still stored. To confirm, run:

# View the full datetime string for the first few rows
head(format(bp_chartevents_itemid$datetime, "%m/%d/%Y %H:%M:%S"))

If this shows the time part, then the data was never lost—it's just how R displays it by default.

4. Force the datetime format in your SQL query #

If all else fails, explicitly format the datetime as a string in your SQL query, then convert it back to a datetime type in R:
First, adjust your SQL query:

SELECT DISTINCT itemid, TO_CHAR(datetime, 'MM/DD/YYYY HH24:MI:SS') AS datetime 
FROM chartevents WHERE valueuom = 'mmHg';

Then in R, convert the string to a POSIXct datetime:

bp_chartevents_itemid$datetime <- as.POSIXct(bp_chartevents_itemid$datetime, format = "%m/%d/%Y %H:%M:%S")

Give these steps a try—one of them should get your time components back!

内容的提问来源于stack exchange，提问作者MeeraWhy