Thread: data type troubles
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.
| 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 Mon, Dec 17, 2012 at 2:38 PM, Kirk Wythers <wythe001@umn.edu> 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.
Giant lines deleted. OK, it looks like the 1332471600 is a unix
timestamp, not a SQL timestamp.
First thing I'd do is make a staging table to hold all these rows.
Declare that second field as bigint and so on, so you can load all
your rows at once.
After all the rows are loaded into a staging table, then you can start
using various pg functions to convert from unix timestamp to sql
timestamp etc.
http://www.postgresql.org/docs/8.4/static/functions-datetime.html
Search for the keyword "epoch" on that page for how to convert back
and forth from unix timestamps.