How to load historical weather data into any database

May 5, 2026

Historical weather data is often most useful when it can be combined with other business, operational, research, or sensor data in a database.

For example, you might want to compare weather with:

Sales
Energy consumption
Insurance claims
Transportation activity
Equipment failures
Agricultural production
Customer behavior
Construction activity
Employee productivity

Visual Crossing Weather can provide historical weather in analysis-ready formats that can be loaded into PostgreSQL, MySQL, MariaDB, Microsoft SQL Server, Oracle, cloud data warehouses, and most other relational or analytical databases.

There are two common ways to build this workflow:

Download weather data as CSV and import it into your database.
Retrieve weather automatically using the Timeline Weather API as part of an ETL or data pipeline.

This article demonstrates both approaches.

If you do not already have a Visual Crossing account, you can sign up for Visual Crossing Weather.

Choosing between CSV and direct API ingestion

The best approach depends on whether the database load is a one-time task or an ongoing process.

CSV is best for one-time and occasional imports

A CSV workflow is often easiest when you need to:

Load a historical dataset once
Perform an ad hoc analysis
Import data using database administration tools
Manually inspect the data before loading it

Visual Crossing Weather Query Builder lets you choose the locations, dates, weather elements, units, and resolution before downloading the resulting dataset.

You can start from Visual Crossing Weather Data.

The Weather API is best for recurring ETL

For recurring or automated database updates, the Timeline Weather API is generally the better solution.

An application can:

Determine which dates need to be loaded.
Request those records from the Weather API.
Parse the CSV or JSON response.
Insert new records or update existing records.
Repeat the process when additional weather data is required.

This avoids manually downloading and importing a new file each time.

Decide what weather data you need

Before creating a database table, decide whether your application requires daily or hourly weather.

Daily weather

Daily data is appropriate for many business and analytical applications.

Common fields include:

datetime
tempmax
tempmin
temp
humidity
precip
snow
snowdepth
windgust
windspeed
winddir
pressure
cloudcover
conditions

Daily datasets are compact and work well for applications such as:

Sales analysis
Agricultural analysis
Energy demand
Insurance analysis
Seasonal reporting
Long-term historical studies

Hourly weather

Hourly data should be used when the timing of conditions during the day matters.

Typical fields include:

datetime
temp
feelslike
humidity
dew
precip
snow
windgust
windspeed
winddir
pressure
cloudcover
visibility
conditions

Hourly weather is useful for:

Incident analysis
Transportation
Event operations
Energy load analysis
Manufacturing
Equipment monitoring
Detailed storm analysis

Hourly datasets contain substantially more records than daily datasets, so choose the resolution that matches the analytical question.

Downloading historical weather as CSV

For an interactive workflow, open Visual Crossing Weather Data and create your dataset using Query Builder.

Choose:

One or more locations
The historical date range
Daily or hourly data
The weather elements you need
Your preferred unit group

Then download the result as CSV.

CSV is supported by nearly every relational database and data-loading tool.

If you are building the process programmatically, you can also request CSV directly from the Timeline Weather API.

For example:

https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/London,UK/2026-01-01/2026-01-31?unitGroup=metric&include=days&elements=datetime,tempmax,tempmin,temp,humidity,precip,windspeed,windgust,winddir,pressure&contentType=csv&key=YOUR_API_KEY

This returns one daily record for each date.

For hourly data, change:

include=days

to:

include=hours

and choose the elements appropriate for hourly records.

Designing the database

There are many possible database designs. For a small project, a single weather table may be sufficient.

For larger systems, it is usually better to separate locations from weather records.

A simple design might contain:

weather_location
weather_daily
weather_hourly

This avoids repeatedly storing location metadata on every weather row and provides a stable key for joining weather with business data.

Creating a location table

A location table can identify the geographic points for which weather is being stored.

For example:

CREATE TABLE weather_location (
    location_id VARCHAR(100) PRIMARY KEY,
    address VARCHAR(500),
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6)
);

location_id should be an identifier meaningful to your own application.

Examples might include:

STORE_001
WAREHOUSE_12
FARM_A
AIRPORT_IAD

Using your own stable location identifier is generally preferable to using a display address as the database key.

Creating a daily weather table

A basic daily table might look like:

CREATE TABLE weather_daily (
    location_id VARCHAR(100) NOT NULL,
    datetime DATE NOT NULL,
    tempmax DOUBLE PRECISION,
    tempmin DOUBLE PRECISION,
    temp DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    precip DOUBLE PRECISION,
    snow DOUBLE PRECISION,
    snowdepth DOUBLE PRECISION,
    windgust DOUBLE PRECISION,
    windspeed DOUBLE PRECISION,
    winddir DOUBLE PRECISION,
    pressure DOUBLE PRECISION,
    cloudcover DOUBLE PRECISION,
    conditions VARCHAR(255),
    PRIMARY KEY (location_id, datetime)
);

The exact numeric and text types vary somewhat between database platforms.

For example, some databases use:

FLOAT
REAL
DOUBLE
NUMBER

for floating-point values.

Likewise, the exact text and date types may differ.

Use the types appropriate for your database platform and application.

Why use a composite primary key?

For most location-based historical weather databases, this combination should identify a daily record:

location_id + datetime

For example:

STORE_001 + 2026-01-15

This provides an important advantage when weather is loaded repeatedly.

Instead of accidentally creating duplicates, your ETL process can:

Insert a record that does not exist.
Update or replace a record that already exists.

This is particularly useful for recent historical weather because upstream observations can occasionally be revised or supplemented after their initial availability.

Creating an hourly weather table

Hourly weather requires both date and time.

A simple table could be:

CREATE TABLE weather_hourly (
    location_id VARCHAR(100) NOT NULL,
    datetime TIMESTAMP NOT NULL,
    temp DOUBLE PRECISION,
    feelslike DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    dew DOUBLE PRECISION,
    precip DOUBLE PRECISION,
    snow DOUBLE PRECISION,
    windgust DOUBLE PRECISION,
    windspeed DOUBLE PRECISION,
    winddir DOUBLE PRECISION,
    pressure DOUBLE PRECISION,
    cloudcover DOUBLE PRECISION,
    visibility DOUBLE PRECISION,
    conditions VARCHAR(255),
    PRIMARY KEY (location_id, datetime)
);

For hourly and sub-daily datasets, pay particular attention to time zones.

Visual Crossing weather dates and times are normally returned in the local time of the requested location. If your database combines data from many time zones, consider also storing an unambiguous epoch timestamp or converting your internal analytical timestamp to UTC.

See Dates and Times in the Weather API for more information.

Do not assume every weather field is always populated

Weather availability varies by:

Location
Date
Weather element
Observation source
Requested resolution

A database schema should therefore normally allow weather fields to contain NULL.

A missing temperature, precipitation, or wind value should not be converted automatically to zero.

For example:

precip = 0

means that the precipitation amount is zero.

But:

precip = NULL

means that a precipitation value is unavailable.

These have very different meanings.

For current coverage guidance, see the Visual Crossing Weather Data Availability Guide.

Loading CSV into PostgreSQL

PostgreSQL can load a CSV file using COPY.

For example:

COPY weather_daily (
    datetime,
    tempmax,
    tempmin,
    temp,
    humidity,
    precip,
    windspeed,
    windgust,
    winddir,
    pressure
)
FROM '/data/weather.csv'
WITH (
    FORMAT CSV,
    HEADER TRUE
);

The file must be accessible to the PostgreSQL server when using server-side COPY.

For client-side imports, PostgreSQL tools such as psql also provide \copy.

Your CSV column order must match either the table definition or the column list supplied in the import command.

Loading CSV into MySQL or MariaDB

MySQL and MariaDB support CSV imports using LOAD DATA.

For example:

LOAD DATA INFILE '/data/weather.csv'
INTO TABLE weather_daily
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Depending on the operating system and database configuration, you may instead need:

LOAD DATA LOCAL INFILE

The database account and server configuration determine which files can be loaded.

Notice that the line terminator is:

\n

not:

/n

which was an error in the older version of this article.

Loading CSV into Microsoft SQL Server

SQL Server supports file imports using BULK INSERT.

For example:

BULK INSERT weather_daily
FROM 'C:\data\weather.csv'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2
);

Exact options depend on the SQL Server version, operating system, file encoding, and where the CSV file is stored.

SQL Server also provides graphical import tools if you prefer to create the initial load interactively.

For recurring production imports, a scripted ETL or integration process is generally easier to automate and monitor.

Loading CSV into Oracle

Oracle provides several mechanisms for importing delimited data, including SQL*Loader and external-table functionality.

The same general process applies:

Create a destination table.
Match the CSV columns to database columns.
Configure delimiters and quoting.
Skip or interpret the header row.
Load the records.
Validate row counts and data types.

If the database platform provides a reliable native CSV importer, there is generally no need to transform Visual Crossing CSV into another intermediate format first.

Other databases and cloud data platforms

The same weather datasets can be loaded into many other systems, including:

Snowflake
Amazon Redshift
Google BigQuery
Databricks
SQLite
DuckDB
IBM Db2
Microsoft Access
Other SQL and analytical databases

The database-specific loading syntax varies, but the fundamental workflow remains the same:

Visual Crossing Weather
        ↓
CSV or Timeline API
        ↓
ETL / ingestion process
        ↓
Weather table
        ↓
Business or analytical queries

Loading weather directly from the Timeline Weather API

For recurring processes, downloading a CSV file manually is usually unnecessary.

Instead, your ETL application can call the Timeline Weather API directly.

The basic endpoint structure is:

/timeline/[location]/[date1]/[date2]

For example:

https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/London,UK/2026-01-01/2026-01-31?unitGroup=metric&include=days&elements=datetime,tempmax,tempmin,temp,humidity,precip,windspeed,windgust,winddir,pressure&key=YOUR_API_KEY

The default response is JSON.

This is convenient for most programming languages because each weather record can be parsed and inserted directly into the database.

You can also request:

contentType=csv

if CSV is more convenient for your ingestion pipeline.

For complete request and response details, see the Timeline Weather API documentation.

Request only the fields you need

A production data pipeline should normally specify the fields required by the application using:

elements=

For example:

elements=datetime,tempmax,tempmin,temp,precip,windspeed

Reducing the element list makes the resulting dataset easier to understand and reduces unnecessary data transfer and database columns.

If you later require another field, the dataset and schema can be extended.

For example:

elements=datetime,tempmax,tempmin,temp,precip,windspeed,humidity,dew

Use a consistent unit group

The Timeline Weather API supports unit groups including:

us
metric
uk
base

For example:

unitGroup=metric

A database should ideally use one consistent unit system.

Changing units between loads without recording that change can make the resulting data unusable.

For example, mixing:

68°F

and:

20°C

in the same temp column without recording the unit would create ambiguous data.

Choose the unit group before the first production load and use it consistently.

Building an incremental weather ETL process

A production pipeline generally should not request the entire historical dataset every time it runs.

Instead, determine which records are missing or need updating.

For example, suppose your database contains data through:

2026-08-19

and you now want to update it through:

2026-08-21

The ETL process can request only the required period.

Conceptually:

1. Query database for latest loaded date
2. Determine required start and end dates
3. Call Timeline Weather API
4. Parse returned records
5. Insert or update database rows
6. Validate load

For some applications, you may intentionally reload a small recent period so that recent observations can be updated if more complete source data becomes available.

Insert versus update

For recurring loads, avoid assuming that every retrieved weather row is new.

Use your database platform’s appropriate insert-or-update mechanism.

Depending on the database this might be called:

UPSERT
MERGE
INSERT … ON CONFLICT
INSERT … ON DUPLICATE KEY UPDATE

For example, PostgreSQL can use:

INSERT INTO weather_daily (
    location_id,
    datetime,
    tempmax,
    tempmin,
    temp,
    precip
)
VALUES (
    'STORE_001',
    '2026-08-20',
    86.4,
    67.2,
    76.1,
    0.18
)
ON CONFLICT (location_id, datetime)
DO UPDATE SET
    tempmax = EXCLUDED.tempmax,
    tempmin = EXCLUDED.tempmin,
    temp = EXCLUDED.temp,
    precip = EXCLUDED.precip;

This allows the database to accept both new records and updated versions of existing records.

Loading weather for multiple business locations

Many database projects involve weather for hundreds or thousands of business locations.

Examples include:

Store ID
Warehouse ID
Customer site
Facility ID
Farm ID
Asset ID

Keep your own application identifier associated with every requested weather location.

For example:

location_id	address	latitude	longitude
STORE_001	Reston, VA	38.96	-77.35
STORE_002	Richmond, VA	37.54	-77.44
STORE_003	Baltimore, MD	39.29	-76.61

Then store weather using:

location_id + datetime

This makes joins with your business tables straightforward.

For example:

SELECT
    s.sale_date,
    s.location_id,
    s.sales,
    w.tempmax,
    w.precip
FROM daily_sales s
LEFT JOIN weather_daily w
    ON s.location_id = w.location_id
   AND s.sale_date = w.datetime;

You can then analyze whether temperature, precipitation, snow, wind, or other weather conditions relate to business performance.

Store the resolved location when location accuracy matters

When you request weather using an address or place name, Visual Crossing resolves that location to geographic coordinates.

For applications where location accuracy is important, retain the resolved latitude and longitude associated with your location definition.

This is especially useful when:

Addresses can be ambiguous
Locations move
Postal codes represent large areas
Business systems contain inconsistent address formatting

For permanent assets, using explicit latitude and longitude can eliminate geocoding ambiguity.

Validate your first load

Before loading millions of records, test a small representative dataset.

Check:

Location
Date range
Daily versus hourly resolution
Units
Column names
Null handling
Time zone behavior
Database data types
Row counts

Also compare a few database rows with the source response.

A small test can detect problems such as:

Fahrenheit values being interpreted as Celsius
Dates shifted by a timezone conversion
Header rows being loaded as data
Empty values becoming zero
Columns mapped in the wrong order

Weather API usage limits

Visual Crossing plans have usage and request limits.

Automated database applications should be designed to operate within the limits of the selected account plan.

For example, avoid repeatedly requesting the same large historical period when only a small number of new records need to be added.

An incremental ETL design normally produces a cleaner database workflow and reduces unnecessary API requests.

For current plan details, see Visual Crossing Weather pricing.

Historical weather can be updated

Recent historical weather is not necessarily permanently fixed immediately after the observation occurs.

Additional observations or corrected upstream data can become available later.

If exact recent historical values are important to your application, design the database key and ETL process so recent records can be updated rather than assuming that the first value retrieved must remain unchanged forever.

CSV or JSON?

Both formats can work well for database ingestion.

Use CSV when:

Your database has a native bulk CSV importer.
You need a one-time historical load.
You are moving large flat tables.
You want to inspect the data easily.

Use JSON when:

Your application calls the API directly.
You want structured location metadata.
You need multiple API response sections.
Your programming language already has strong JSON support.

For a straightforward daily weather fact table, CSV is often the easiest bulk format.

For an automated API-driven ETL process, JSON is often the easiest application format.

Example architecture

A typical production database integration might look like:

Business location table
        ↓
ETL application
        ↓
Visual Crossing Timeline Weather API
        ↓
Parse and validate weather records
        ↓
UPSERT into weather_daily / weather_hourly
        ↓
Join with business data
        ↓
Reporting / analytics / machine learning

This architecture keeps weather acquisition separate from downstream analysis.

It also allows you to change reports and business logic without repeatedly retrieving the source weather data.

Getting started

For a one-time project:

Open Visual Crossing Weather Data.
Enter the required locations.
Select the historical period.
Choose daily or hourly weather.
Select the elements you need.
Download CSV.
Create a matching database table.
Import the CSV.
Validate the resulting records.

For an automated project:

Create your location table.
Create the weather destination table.
Obtain your Visual Crossing API key.
Build a Timeline Weather API request.
Request only the required dates and elements.
Parse the returned data.
Insert or update the database rows.
Schedule the ETL process according to your application’s requirements.

See Getting Started with the Weather API if you are new to the Timeline Weather API.

Summary

Historical weather can be loaded into virtually any modern database.

For one-time or occasional analysis, the simplest workflow is:

Visual Crossing Query Builder
→ CSV
→ database bulk import

For recurring production systems, use:

Timeline Weather API
→ ETL process
→ insert/update database records

Use current Timeline weather element names such as:

tempmax
tempmin
temp
humidity
precip
snow
windspeed
windgust
winddir
pressure

Choose daily or hourly resolution according to the analytical question, retain a stable business location_id, allow missing weather fields to remain NULL, use a consistent unit group, and create a unique database key based on location and time.

Once the weather data is in your database, it can be joined directly with business, operational, research, or sensor data to analyze how weather affects the outcomes that matter to your organization.

For interactive weather-data access, visit Visual Crossing Weather Data.

For programmatic access, see the Timeline Weather API documentation.

For current data coverage, see the Visual Crossing Weather Data Availability Guide.

If you have questions about a specific database integration or dataset, contact Visual Crossing Support.