Adjusting sql schema to include timezones with timestamps
CREATE TABLE owners (
guid UUID PRIMARY KEY,
name VARCHAR(64) UNIQUE NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE jobs (
guid UUID PRIMARY KEY,
name VARCHAR(64) UNIQUE NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
+ updated_at TIMESTAMP with time zone,
current_version INTEGER,
current_ownership INTEGER,
- nominal_time TIMESTAMP,
+ nominal_time TIMESTAMP with time zone,
category VARCHAR(64),
description TEXT NOT NULL
);
CREATE TABLE ownerships (
guid UUID PRIMARY KEY,
- started_at TIMESTAMP,
- ended_at TIMESTAMP,
+ started_at TIMESTAMP with time zone,
+ ended_at TIMESTAMP with time zone,
job_guid UUID REFERENCES jobs(guid),
owner_guid UUID REFERENCES owners(guid)
);
CREATE TABLE job_versions (
guid UUID PRIMARY KEY,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
+ updated_at TIMESTAMP with time zone,
input_dataset VARCHAR(64) NOT NULL,
output_dataset VARCHAR(64) NOT NULL,
job_guid UUID REFERENCES jobs(guid),
@@ -38,19 +38,19 @@ CREATE TABLE job_versions (
CREATE TABLE job_runs (
guid UUID PRIMARY KEY,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
job_version_guid UUID REFERENCES job_versions(guid),
run_guid VARCHAR(255) UNIQUE NOT NULL,
- started_at TIMESTAMP,
- ended_at TIMESTAMP,
+ started_at TIMESTAMP with time zone,
+ ended_at TIMESTAMP with time zone,
input_dataset_version_guid UUID,
output_dataset_version_guid UUID,
- latest_heartbeat TIMESTAMP
+ latest_heartbeat TIMESTAMP with time zone
);
CREATE TABLE job_run_states (
guid UUID PRIMARY KEY,
- transitioned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ transitioned_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
job_run_guid UUID REFERENCES job_runs(guid),
state INTEGER
);
@@ -58,8 +58,8 @@ CREATE TABLE job_run_states (
CREATE TABLE datasets (
guid UUID PRIMARY KEY,
name VARCHAR(64) UNIQUE NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
+ updated_at TIMESTAMP with time zone,
type INTEGER,
origin INTEGER,
current_version INTEGER,
@@ -69,7 +69,7 @@ CREATE TABLE datasets (
CREATE TABLE dbs (
guid UUID PRIMARY KEY,
name VARCHAR(64) UNIQUE NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
type INTEGER,
connection_url VARCHAR(255),
description TEXT NOT NULL
@@ -77,7 +77,7 @@ CREATE TABLE dbs (
CREATE TABLE db_table_versions (
guid UUID PRIMARY KEY,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
dataset_guid UUID REFERENCES datasets(guid),
db_guid UUID REFERENCES dbs(guid),
description TEXT NOT NULL
@@ -85,7 +85,7 @@ CREATE TABLE db_table_versions (
CREATE TABLE iceberg_table_versions (
guid UUID PRIMARY KEY,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
dataset_guid UUID REFERENCES datasets(guid),
previous_snapshot_id BIGINT,
current_snapshot_id BIGINT,
