[R] is.na() == TRUE for POSIXlt time / date of "2014-03-09 02:00:00"

[John McKown]

Probably not the best, but here:

con <- odbcConnect("BMC");
timezone <- Sys.timezone();
#
query=paste0("select CONVERT(smalldatetime,Int_Start_Date,11) as
Int_Start_Date,",
             " CONVERT(smalldatetime,CASE WHEN Int_Start_Time is NULL
then '00:00' ",
             "else
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,"'",
             "AND INTTYPE='M'"
);
cpintvl <- sqlQuery(con,
            query,
            stringsAsFactors=FALSE,
            as.is=TRUE);
#
# 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),

substr(cpintvl$Int_Start_Time,12,19)),"%Y-%m-%d %H:%M:%S");

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
in UTC.

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
>> data.
>>
>>>
>>>
>>> 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!
Genghis Khan

Maranatha! <><
John McKown