[R] is.na() == TRUE for POSIXlt time / date of "2014-03-09 02:00:00"
john.archie.mckown at gmail.com
Wed Jul 30 20:39:07 CEST 2014
Probably not the best, but here:
con <- odbcConnect("BMC");
timezone <- Sys.timezone();
query=paste0("select CONVERT(smalldatetime,Int_Start_Date,11) as
" CONVERT(smalldatetime,CASE WHEN Int_Start_Time is NULL
then '00:00' ",
LEFT(Int_Start_Time,2)+':'+SUBSTRING(Int_Start_Time,3,2) end +",
"':00', 14) as Int_Start_Time",
", Int_duration, RTRIM(INTTYPE) AS INTTYPE",
", RTRIM(Int_descr) AS Int_descr",
", RTRIM(INTSUBT) as INTSUBT",
", INDEXX, RTRIM(Label) AS Label",
", RTRIM(CHANGED) AS CHANGED",
", RTRIM(ALERT) AS ALERT",
", RTRIM(RELEASE) AS RELEASE",
" FROM CPINTVL where Int_Start_Date BETWEEN '",
startDateChar,"' and '",endDateChar,"'",
cpintvl <- sqlQuery(con,
# properly combine start date and time because I couldn't figure out how to
# get MS-SQL to do it for me.
cpintvl$Int_Start <- strptime(paste0(substr(cpintvl$Int_Start_Date,1,11),
So the actual value was created with the strptime() call at the end.
The rest is just in character form. The Int_Start_Date in the DB is in
yy/mm/dd format as a character field. Int_Start_Time is HHMM as a
character field with leading zeros. The return value from the SELECT
has Start_Int_Date formatted in yyyy-mm-dd as a character string.
Start_Int_Time formatted in hh:mm:ss as a character string.
In any case, you have accurately explained my foolishness. I keep
forgetting about DST because I record _everything_ in my personal DBs
On Wed, Jul 30, 2014 at 1:23 PM, William Dunlap <wdunlap at tibco.com> wrote:
> I meant what R commands did you use to change the database's version
> of the time/date object to the R version?
> Bill Dunlap
> TIBCO Software
> wdunlap tibco.com
> On Wed, Jul 30, 2014 at 11:07 AM, John McKown
> <john.archie.mckown at gmail.com> wrote:
>> On Wed, Jul 30, 2014 at 12:54 PM, William Dunlap <wdunlap at tibco.com> wrote:
>>>> I should have mentioned that I tried other time stamps, generated the
>>>> same way as "q" above.
>>> How did you generate q and in what time zone were you?
>> I got it from an MS-SQL data base which is maintained by some
>> closed-source vendor software. But I manipulate the data in the SELECT
>> before sending it to R via ODBC. I need to double check the raw data
>> in the data base.
>>> Note that 2am
>>> on 9 March 2014 is when 'daylight savings time' started in the parts
>>> of the US where it is observed. Does 2am exist or do we jump from
>>> 1:59:59 to 3:00:00?
>> Hum, that hadn't occurred to me. I need to see what is in the DB.
>> But I think you have found my problem. If I force the timezone to be
>> GMT, then the problem disappears. So that is what I'll do with this
>>> Bill Dunlap
>>> TIBCO Software
>>> wdunlap tibco.com
>> There is nothing more pleasant than traveling and meeting new people!
>> Genghis Khan
>> Maranatha! <><
>> John McKown
There is nothing more pleasant than traveling and meeting new people!
More information about the R-help