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

Question

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!

What is the class of result.rows[0].start_date? I'm guessing it's Date. What Javascript calls Date is really a timestamp. So your PostgreSQL Date is being turned into a Javascript Date (timestamp). Also, use of moment is being discouraged. — Schwern
– Schwern, Commented Feb 12 at 3:51

Sharp Dev · Accepted Answer · 2025-02-12 04:04:59Z

2

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.

answered Feb 12 at 4:04

Sharp Dev

1,0701 gold badge10 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Schwern Feb 13 at 1:56

Rather than doing it every query, you can set the time zone for the whole session once when you connect.

Sharp Dev Feb 13 at 5:59

@Schwern ah that's a very good point, setting it once when the instance connects is the proper solution.

Collectives™ on Stack Overflow

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

1 Answer 1

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

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related