0

If I read out a field of type "datetime" it returns a character vector. Is it the genral way or do I have to notice something? This post came up with a similar question.

Community
  • 1
  • 1
Klaus
  • 1,946
  • 3
  • 19
  • 34
  • Based on the question you linked to, I suppose you're using MySQL and the R package RMySQL, but why you failed to include that information explicitly in your question is beyond me. Don't you _want_ help? – joran Sep 03 '13 at 14:35
  • @joran I use RJDBC and that uses the DBI package for calls like dbGetQuery, so I suggest that this call is independent from the driver. – Klaus Sep 03 '13 at 14:38
  • Well, see, that's pretty relevant information. Different drivers handle data type conversion differently. – joran Sep 03 '13 at 14:40
  • You are right, sry but from there on, what should I do now? I want to know if its possible to get a POSIXlt vector instead of character vector. – Klaus Sep 03 '13 at 15:29
  • 1
    My guess would be no, but I don't have any experience with RJDBC. I don't think that DBI does any conversion itself; all the other db interfaces I've used from R (RODBC, ROracle and RSQLite) do some attempt at conversion, but I don't know what, if anything RJDBC does. – joran Sep 03 '13 at 15:41

1 Answers1

2

In the DBI vignette (vignette("DBI")), Chapter 4 ("Data Type Mappings"), it states

By default dates and date-time objects are mapped to character using the appropriate TO_CHAR function in the DBMS (which should take care of any locale information).

I can't find any suggestion that RJDBC overrides this behaviour, so you'll have to manually convert your dates. Take a look at strptime, or the wrappers in the lubridate package.

Richie Cotton
  • 118,240
  • 47
  • 247
  • 360