Date shows up as number

Question

I have fetched a set of dates from postgresql, they look correct:

[1] "2007-07-13" "2007-07-14" "2007-07-22" "2007-07-23" "2007-07-24"
[6] "2007-07-25" "2007-08-13" "2007-08-14" "2007-08-15" "2007-08-16"
etc.

Then I want to run a loop on them to make new sql sentences to fetch some other data sets (yes, I know what I am doing, it would not have been possible to do all the processing in the database server)

So I tried

for(date in geilodates)
 mapdate(date,geilo)
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  invalid input syntax for type date: "13707"
LINE 1: ...id_date_location where not cowid is null and date='13707' or...

mapdate is a function I have written, the use of date within that is

sql=paste('select * from gps_coord where cowid=',cowid," and date='",date,"'",sep='')

So, what has happened is that R silently converted my formatted dates to their integer representations before i tried to paste the sql together.

How do I get the original textual representation of the date? I tried

for(date in geilodates){
  d=as.Date(date,origin="1970-01-01")
  mapdate(d,geilo)
}
Error in charToDate(x) : 
character string is not in a standard unambiguous format

And I have not managed to find any other functions to create a datestring (or to "serve" the date as the string I get when listing the variable

Could you show both `dput(geilodates)` and the full `mapdate` function? — David Robinson, Jan 24 '13 at 08:39
Related: http://stackoverflow.com/questions/6434663/r-why-does-looping-over-a-date-object-result-in-a-numeric-iterator — Joshua Ulrich, Jan 24 '13 at 14:59

score 7 · Accepted Answer · answered Jan 24 '13 at 09:10

7

Thanks to wush978 for pointing me in the right direction, In the end I had to do:

for(d in geilodates){
 date=format(as.Date(d,origin="1970-01-01"))
 mapdate(date,geilo)
}

For some reason, inside the loop the "date" variable was seen as an integer, so I explicitely had to convert it to a date and then format it...

answered Jan 24 '13 at 09:10

MortenSickel

2,118
4
26
44

2

"...for some reason" is because `is.vector(Sys.Date())` is `FALSE`. `help('for')` says: "seq: An expression evaluating to a vector...or to a pairlist or 'NULL'", so what happens is (essentially) `as.vector(Sys.Date()) # 15729`. – Joshua Ulrich Jan 24 '13 at 14:55
Thanks, @JoshuaUlrich! That clearified it. (jsut hope I remember that until the next time I need it ;-) ) – MortenSickel Jan 24 '13 at 14:57

score 5 · Answer 2 · edited Jan 24 '13 at 09:08

5

try ?format.Date

x <- Sys.Date()
class(x)
class(format(x))

In R, the data of class Date is a numeric type. An official way to represent Date as string is to call format.

I doubt that the format of date is defined in your case, so paste does something unexpected.

Maybe you need to put format(x, "%Y-%m-%d") in your paste function instead of date to tell R how which format you want for Date.

edited Jan 24 '13 at 09:08

Richie Cotton

118,240
47
247
360

answered Jan 24 '13 at 08:44

wush978

3,114
2
19
23

1

Thanks, looks like what I needed, but this is weird, I can do: date=geilodates[1] > date [1] "2007-07-13" > class(date) [1] "Date" > d=format.Date(date,origin="1970-01-01") > d [1] "2007-07-13" > class(d) [1] "character" That looks great, but if I put it into the for-loop: for(date in geilodates){ + d=format.Date(date,origin="1970-01-01") + } Error in as.POSIXlt.numeric(x) : 'origin' must be supplied – MortenSickel Jan 24 '13 at 08:56
@MortenSickel, Maybe you need to track the class of involving variables. – wush978 Jan 24 '13 at 09:08

Date shows up as number

2 Answers2

Linked