timestamps w/o time zone

[hmt]

This is driving me nuts. I'm trying to set a date as timestamp coming from an input form:

I'm getting the date right from the DB (type timestamp w/o time zone):

let start = new Date(start_date).toISOString().replace('Z','');

Then I'm binding it to my input value (svelte code here, but you'll get how it works):

   <input
	type="datetime-local"
	bind:value={start}
	on:change="{reset_start}">

So nothing spectacular here. Now setting this date via sql:

await sql`UPDATE foo SET start = ${start} RETURNING *`;

It will for some reason add a time zone calculation and because I'm not CET the timestamp is off by two hours.

[porsager]

That's probably due to the time zone of your database.

Try to post the output of running

SELECT current_setting('TIMEZONE');

You can set it with the connection options like this:

const sql = postgres({
  connection: {
    timezone: 'UTC'
  }
})

[PierBover]

Hey @porsager I'm wondering what will happen when setting the timezone in the connection options.

Will the client convert the dates into JS Date objects with a given timezone?

What will happen with timestamp and timestampz values?

[hmt]

Thank you, I did as advised:

export const sql = postgres({
  connection: {
    timezone: 'Europe/Berlin'
  }
});

And also have set the correct time zone in my db setting:

=> SELECT current_setting('TIMEZONE');
 current_setting
-----------------                                                                                                                               
 Europe/Berlin                                                                                                                                     
(1 row) 

There is still the two hour difference in between.

[porsager]

This issue doesn't seem related to Postgres.js but seems more about a svelte/browser issue 😋 Well. Let's see if we can fix it anyway

Try to post the actual value you set in the input value field, and what you'd expect to see.

[porsager]

Also you should almost never use timestamp without time zone in Postgres

You remove Postgres ability to understand time zones, which could very well be causing your issue.

Try to use timestamptz and don't do anything special anywhere else. Remove the toISOstring and z replace and pass dates directly.

[hmt]

sorry, don't want to ruin your Friday night...

This is what I'm setting:

console.log(`UPDATE foo SET start = ${data.start} RETURNING *`)
res = await sql`UPDATE foo SET start = ${data.start} RETURNING *`;

The log looks fine:

UPDATE foo SET start = 2022-03-21T00:00 RETURNING *

In the browser (current FF) I'm also logging the value which is sent: 2022-03-21T00:00
The returned value however is start: "2022-03-20T22:00:00.000Z"

Checking the value via psql:

> select start from foo; 
 start                                                                                                                           
---------------------                                                                                                                      
 2022-03-20 23:00:00                                                                                                            
(1 row)   

Now everything is mixed up. I think for a while I had at least the correct value via psql :S

[hmt]

ok, will change types to time zone now to see if that fixes it.

[porsager]

Haha. Don't worry - Time is a mess 🤣

[hmt]

ok, got it. I do need to strip the "Z" otherwise the browser ignores the date.
I have set my server to "Europe/Berlin" now with the type timestamp with time zone. Before I set the timestamp I'm adding the "Z" to the string again and I seem to be ok now. Reading the date with date.toLocaleDateString() I need to set it to UTC. I don't get it but it's ok, it does the job.

[porsager]

Won't svelte accept an actual js Date object?

[hmt]

Don't think it's svelte related: https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/datetime-local
Try adding the Z