CODEWITHSUNDEEP
postgresqlnext.jsvercel
rapidoodle
rapidoodle

Reputation: 350

PostgreSQL (Neon/Vercel) always returns TIMESTAMP instead of DATE — How to fix this?

I'm using Vercel Postgres (Neon), and my table has a column start_date stored as a DATE type. However, when I query it, PostgreSQL always returns a full timestamp (YYYY-MM-DDTHH:MM:SSZ) instead of just the YYYY-MM-DD date.

Table Schema:

CREATE TABLE share_buyback_programs (
        id SERIAL PRIMARY KEY,
        equity_isin TEXT,
        start_date DATE,  -- Stored as DATE, but returns TIMESTAMP
        end_date DATE,
        size NUMERIC
    );

SQL Query:

SELECT start_date FROM share_buyback_programs;

Expected Output:

2025-02-07

Actual Output:

2025-02-06T16:00:00.000Z

This happens even though the column is of type DATE, not TIMESTAMP.

What I've Tried: Casting as TEXT → Still returns a timestamp in some cases:

SELECT start_date::TEXT FROM share_buyback_programs;

Adjusting time zones (AT TIME ZONE):

SELECT start_date AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Manila' FROM share_buyback_programs;

Checking the actual stored values:

SELECT column_name, data_type FROM information_schema.columns 
WHERE table_name = 'share_buyback_programs' AND column_name = 'start_date';
Confirms it's stored as DATE, not TIMESTAMP.

Trying to fix it in JavaScript using moment.js:

import moment from "moment";

const result = await sql`SELECT start_date FROM share_buyback_programs`;

const formattedDate = moment(result.rows[0].start_date).format("YYYY-MM-DD");

console.log(formattedDate); // Still logs 2025-02-06, not 2025-02-07

My Environment:

Database: PostgreSQL (Neon via Vercel)
Driver: @vercel/postgres
Timezone Settings: Vercel/Neon defaults to UTC
Query Language: Node.js / Next.js API Route

Question: Why does Neon/Vercel Postgres return a TIMESTAMP instead of a DATE for start_date? How can I ensure it only returns YYYY-MM-DD without manually formatting the output every time?

Any help is appreciated!

Upvotes: 1

Views: 56

Answers (1)

Sharp Dev
Sharp Dev

Reputation: 1045

More of a @vercel/postgres thing
In my experience @vercel/postgres does some processing before returning the result (and in this processing it returns a timestamp). What's more annoying is it will use the server time instead of UTC time. So no matter where the vercel instance is, it will just go with whatever that is (which is awful).

Fixing it by telling Postgres to convert to vercel instance time.

With that said you can adjust your SQL to combat this by telling postgres what timestamp your server is in. Currently you have something like:

const result = await sql`SELECT start_date FROM share_buyback_programs`;

To tell postgres what your specific instance's time is at, you could change it to dynamically querying the timestamp and then send that. That would look something like this:

const timeZone = Intl.DateTimeFormat().resolvedOptions().timeZone;
const { rows } = await sql`SELECT start_date AT TIME ZONE ${timeZone} FROM share_buyback_programs;`;

This will give you back a timestamp which in your example would look like: 2025-02-07T00:00:00.000Z and you can use moment like you show above to get the date, or you could even just slice the first 10 characters (does not matter).

You may want to try another package for sql queries.
Given that @vercel/postgres cannot be used with most postgres instances (You're stuck to what they have to offer), if you want to use a different postgres instance, you'll need to then set up a proxy to even connect to the instance... That's more overhead for you to deal with in the future. All of which are more headaches.
With all that said, I recommend a package like pg, it's simple does what you want and can be used with the vercel databases just fine (and any other postgres database for that matter).
Disclaimer: I'm not affiliated with pg, this is my honest recommendation.

Upvotes: 1

Related Questions