Thanks. 

Is there a way to use the float4 data type for a variable when empty records 
contain "NA", instead of nothing? As you can see below the variable "A_TC_AVG1" 
has the first record filled with "NA" signifying that the record is empty? 
Records that contain values will have decimal values like 12.3456. 


ROWID   TIME2   TIMESTAMP       BLOCK   STATNAME        TABLE   PROGRAM 
A_TC_AVG1       A_TC_AVG10      A_TC_AVG11      A_TC_AVG12      A_TC_AVG2       
A_TC_AVG3       A_TC_AVG4       A_TC_AVG5       A_TC_AVG6       A_TC_AVG7       
A_TC_AVG8       A_TC_AVG9       AIRTC_AVG       ECODE1  ECODE10 ECODE11 ECODE12 
ECODE2  ECODE3  ECODE4  ECODE5  ECODE6  ECODE7  ECODE8  ECODE9  ETC_AVG13       
ETC_AVG14       ETC_AVG15       ETC_AVG16       ETC_AVG17       ETC_AVG18       
ETC_AVG19       ETC_AVG20       ETC_AVG21       ETC_AVG22       ETC_AVG23       
ETC_AVG24       ETC_AVG25       FLAG1   FLAG10  FLAG11  FLAG12  FLAG2   FLAG3   
FLAG4   FLAG5   FLAG6   FLAG7   FLAG8   FLAG9   PAR_DEN_AVG     PAR_TOT_TOT     
PERIOD10        PERIOD11        PERIOD12        PERIOD13        PERIOD14        
PERIOD15        PERIOD16        PERIOD9 RAIN_IN_TOT     RH      S_TC_AVG1       
S_TC_AVG10      S_TC_AVG11      S_TC_AVG12      S_TC_AVG2       S_TC_AVG3       
S_TC_AVG4       S_TC_AVG5       S_TC_AVG6       S_TC_AVG7       S_TC_AVG8       
S_TC_AVG9       SLR_MJ_TOT      SLR_W_AVG       SOILTEMP10_AVG  SOILTEMP100_AVG 
SOILTEMP20_AVG  SOILTEMP30_AVG  SOILTEMP40_AVG  SOILTEMP50_AVG  SOILTEMP75_AVG  
VP_AVG  VWC10   VWC11   VWC12   VWC13   VWC14   VWC15   VWC16   VWC9    WCODE1  
WCODE10 WCODE11 WCODE12 WCODE2  WCODE3  WCODE4  WCODE5  WCODE6  WCODE7  WCODE8  
WCODE9  WTC_AVG13       WTC_AVG14       WTC_AVG15       WTC_AVG16       
WTC_AVG17       WTC_AVG18       WTC_AVG19       WTC_AVG20       WTC_AVG21       
WTC_AVG22       WTC_AVG23       WTC_AVG24       WTC_AVG25
2012-03-22 21:00:00_B4WARM_A    1332471600      1332471600      B4WARM_A        
B4WARM_A        B4Warm_60       CPU:B4warm2011_Sauron_ALT4.2.CR1        NA      
NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      
NA      0       NA      NA      NA      NA      NA      NA      NA      NA      
NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      
NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      
NA      NA      NA      NA      NA      NA      NA      NA      NA      0       
0       25.63   24.73   23.27   23.87   23.63   25.09   23.59   21.76   0       
0       NA      NA      NA      NA      NA      NA      NA      NA      NA      
NA      NA      NA      0       0       NA      NA      NA      NA      NA      
NA      NA      0       0.232   0.206   0.166   0.182   0.176   0.216   0.175   
0.128   NA      NA      NA      NA      NA      NA      NA      NA      NA      
NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      
NA      NA      NA      NA      NA      NA




On Dec 17, 2012, at 4:05 PM, Adrian Klaver <adrian.kla...@gmail.com> wrote:

> On 12/17/2012 01:53 PM, Kirk Wythers wrote:
>> 
>> I seem to be dealing with a data type issue when I try and import data
>> into a new and empty database.
>> 
>> Error Message: ERROR:  date/time field value out of range:
>> "1332471600:00:00"
>> LINE 1: ...tc_avg25") values ('2012-03-22 21:00:00_B4WARM_A','133247160…
>> 
>> Here are the first two rows in the files I was sent. I cast ROWID as
>> varchar32, TIME2 as time (I was told that TIME2 was a posix time value),
>> and TIMESTAMP as timestamp.
>> 
>> Any advice that would help me out of this hole is appreciated.
> 
> Assuming by POSIX time you mean seconds since epoch you will need to do 
> something like this:
> 
> test=# SELECT to_timestamp(1332471600);
>      to_timestamp
> ------------------------
> 2012-03-22 20:00:00-07
> 
> For time:
> test=# SELECT to_timestamp('1332471600')::time;
> to_timestamp
> --------------
> 20:00:00
> 
> 
> In your INSERT statement use the to_timestamp() in the VALUES portion.
> -- 
> Adrian Klaver
> adrian.kla...@gmail.com

Reply via email to