136 lines
4.7 KiB
Bash
136 lines
4.7 KiB
Bash
#!/bin/bash
|
||
|
||
# 设置环境变量
|
||
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
|
||
export LANG=en_US.UTF-8
|
||
export LC_ALL=en_US.UTF-8
|
||
|
||
# 设置PostgreSQL环境变量
|
||
export PGPASSWORD="root" # 替换为实际的密码
|
||
|
||
# 设置数据库连接参数
|
||
DB_HOST="8.134.185.53"
|
||
DB_PORT="5432"
|
||
DB_NAME="weatherdb"
|
||
DB_USER="yarnom"
|
||
EXPORT_DIR="/home/yarnom/Archive/code/WeatherStation/exportData"
|
||
|
||
# 添加日志功能
|
||
LOG_FILE="$EXPORT_DIR/export_daily.log"
|
||
|
||
# 检查是否提供了日期参数
|
||
if [ $# -ne 1 ]; then
|
||
echo "使用方法: $0 YYYY-MM-DD"
|
||
echo "示例: $0 2024-02-20"
|
||
exit 1
|
||
fi
|
||
|
||
# 获取输入的日期
|
||
TARGET_DATE="$1"
|
||
|
||
# 验证日期格式
|
||
if ! date -d "$TARGET_DATE" >/dev/null 2>&1; then
|
||
echo "错误：无效的日期格式。请使用 YYYY-MM-DD 格式。"
|
||
exit 1
|
||
fi
|
||
|
||
# 记录开始时间
|
||
echo "=== 开始导出日期 $TARGET_DATE 的数据: $(date) ===" >> "$LOG_FILE"
|
||
|
||
# 确保导出目录存在
|
||
mkdir -p "$EXPORT_DIR/daily_exports"
|
||
|
||
# 设置输出文件
|
||
OUTPUT_FILE="$EXPORT_DIR/daily_exports/weather_data_${TARGET_DATE}.csv"
|
||
|
||
# 创建表头
|
||
echo "latitude,longitude,station_id,station_name,date_time,elevation,pressure,temperature,dewpoint,wind_speed,wind_direction,relative_humidity,ztd,pwv" > "$OUTPUT_FILE"
|
||
|
||
# 导出数据
|
||
psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -A -F "," -t -c "
|
||
WITH time_series AS (
|
||
SELECT generate_series(
|
||
'$TARGET_DATE 00:00:00'::timestamp,
|
||
'$TARGET_DATE 23:59:59'::timestamp,
|
||
'10 minutes'::interval
|
||
) as interval_start
|
||
),
|
||
avg_data AS (
|
||
SELECT
|
||
s.station_id,
|
||
COALESCE(s.password, s.station_id) as output_station_id,
|
||
ts.interval_start as date_time,
|
||
s.latitude,
|
||
s.longitude,
|
||
s.altitude as elevation,
|
||
-- 气压、温度取平均
|
||
ROUND(AVG(r.pressure)::numeric, 2) as pressure,
|
||
ROUND(AVG(r.temperature)::numeric, 2) as temperature,
|
||
-- 风速取平均
|
||
ROUND(AVG(r.wind_speed)::numeric, 2) as wind_speed,
|
||
-- 风向使用矢量平均
|
||
ROUND(DEGREES(ATAN2(
|
||
AVG(SIN(RADIANS(r.wind_direction))),
|
||
AVG(COS(RADIANS(r.wind_direction)))
|
||
))::numeric + CASE
|
||
WHEN DEGREES(ATAN2(
|
||
AVG(SIN(RADIANS(r.wind_direction))),
|
||
AVG(COS(RADIANS(r.wind_direction)))
|
||
)) < 0 THEN 360
|
||
ELSE 0
|
||
END, 2) as wind_direction,
|
||
-- 湿度取平均
|
||
ROUND(AVG(r.humidity)::numeric, 2) as relative_humidity
|
||
FROM time_series ts
|
||
CROSS JOIN stations s
|
||
LEFT JOIN rs485_weather_data r ON s.station_id = r.station_id
|
||
AND r.timestamp >= ts.interval_start
|
||
AND r.timestamp < ts.interval_start + '10 minutes'::interval
|
||
GROUP BY s.station_id, s.password, ts.interval_start, s.latitude, s.longitude, s.altitude
|
||
)
|
||
SELECT
|
||
COALESCE(latitude::text, '0'), -- latitude
|
||
COALESCE(longitude::text, '0'), -- longitude
|
||
output_station_id, -- station_id
|
||
'', -- station_name
|
||
date_time, -- date_time
|
||
COALESCE(elevation::text, '0'), -- elevation
|
||
COALESCE(pressure::text, '0'), -- pressure
|
||
COALESCE(temperature::text, '0'), -- temperature
|
||
'0', -- dewpoint
|
||
COALESCE(wind_speed::text, '0'), -- wind_speed
|
||
COALESCE(wind_direction::text, '0'), -- wind_direction
|
||
COALESCE(relative_humidity::text, '0'), -- relative_humidity
|
||
'', -- ztd
|
||
'' -- pwv
|
||
FROM avg_data
|
||
ORDER BY date_time, output_station_id;" >> "$OUTPUT_FILE" 2>> "$LOG_FILE"
|
||
|
||
# 检查psql执行状态
|
||
if [ $? -eq 0 ]; then
|
||
echo "数据导出成功到文件: $OUTPUT_FILE"
|
||
echo "数据导出成功" >> "$LOG_FILE"
|
||
else
|
||
echo "数据导出失败"
|
||
echo "数据导出失败" >> "$LOG_FILE"
|
||
fi
|
||
|
||
# 记录结束时间
|
||
echo "=== 结束导出: $(date) ===" >> "$LOG_FILE"
|
||
echo "" >> "$LOG_FILE"
|
||
|
||
# 保持日志文件大小合理（保留最后1000行）
|
||
tail -n 1000 "$LOG_FILE" > "${LOG_FILE}.tmp" && mv "${LOG_FILE}.tmp" "$LOG_FILE"
|
||
|
||
# 清除密码环境变量（安全考虑）
|
||
unset PGPASSWORD
|
||
|
||
# 如果导出成功，显示一些统计信息
|
||
if [ $? -eq 0 ]; then
|
||
echo "统计信息："
|
||
echo "----------------------------------------"
|
||
echo "总记录数：$(tail -n +2 "$OUTPUT_FILE" | wc -l)"
|
||
echo "文件大小：$(du -h "$OUTPUT_FILE" | cut -f1)"
|
||
echo "文件位置：$OUTPUT_FILE"
|
||
fi
|