How to Convert Postgresql Time Without Time Zone to Date in JavaScript

const date = new Date("2019-02-04 15:38:22.529");
const time = date.getTime();console.log(time);

Convert timestamp without timezone into timestamp with timezone

You're looking for AT TIME ZONE, which is a little confusing but can do everything you need it to.

To convert a timestamp known to be UTC to a timestamptz:

SELECT '2021-06-24 11:00:00'::timestamp AT TIME ZONE 'UTC';
=> 2021-06-24 06:00:00-05

All timestamptz values are stored internally in Postgres in UTC, and do not retain the timezone they were inserted at, so they will then show up in whatever your connection timezone is. You can cast a timestamptz back to a timestamp in your desired zone by using AT TIME ZONE again (as Erwin pointed out below, AT TIME ZONE always switches between timestamp and timestamptz):

SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'UTC';
=> 2021-06-24 11:00:00

SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'America/Chicago';
=> 2021-06-24 06:00:00

Easiest way to convert YYYYMMDD to the midnight with specific timezone on PostgreSQL?

Strictly speaking, your request is an oxymoron:

I want to get 2021-01-01 00:00:00.000000 in my TZ.

You show a timestamp (timestamp without time zone) literal, which is completely orthogonal to (and ignorant of) the concept of time zones.

But you want it "in my TZ", which would imply to a timestamptz (timestamp with time zone) value, where the corresponding literal includes a time offset like: 2021-01-01 00:00:00.000000+01.

Since the format YYYYMMDD is unambiguous ISO format, you can cast to date or timestamp directly, safely. A cast to timestamp assumes the time component 00:00 automatically. Produces your desired timestamp '2021-01-01 00:00'.

SELECT '20211203'::timestamp;

If you want the result type timestamp, we are done here.

If you want the result type timestamptz, there is a quick-and-dirty shortcut:

SELECT '20211203'::timestamptz;

The current time zone setting is assumed for the type cast. But this introduces a dependency on a runtime settings. Notoriously unreliable, only advisable for situations where you can be certain of the current setting ...

The sure and generally advisable way is to define the target time zone with the AT TIME ZONE construct explicitly. Say, your timezone is 'Europe/Vienna':

SELECT '20211203'::timestamp AT TIME ZONE 'Europe/Vienna';

Use a time zone name. Time zone abbreviations are treacherous for input conversion and may fail for daylight saving time (DST) or other bureaucratic nonsense. 'CET' (Central European Time) is appropriate for timestamps during "standard time". During DST periods, you'd have to use 'CEST' (Central European Summer Time).

_{DST is utter nonsense, but some countries, including the EU, still haven't managed to get rid of it.}

db<>fiddle here - note that dbfiddle runs with time zone GB by default.

Now you have the timestamptz value representing the start of the day (00:00) in your given time zone. Don't be fooled by the display of timestamptz values. That's always adjusted to the current time zone setting, but it always represents that unique point in time, just with different ways to display it.

You do understand that the time zone itself is never stored in a timestamptz value, right? Even though timestamp with time zone sounds like it might. See:

• Time zone storage in data type "timestamp with time zone"

To force a certain display use to_char() or some other functions to generate the desired string. There is a dedicated page Data Type Formatting Functions in the manual.

Related:

• https://www.postgresql.org/docs/current/view-pg-timezone-names.html
• Ignoring time zones altogether in Rails and PostgreSQL

Node ::date doesn't convert timestamp without zone

I don't think the problem is in the database. The PgAdmin display suggests that the database is producing a real date value so that's fine. The problem looks like the value from the database is being converted to a native JavaScript Date. But Dates are actually full timestamps and the time-of-day portion of a Date defaults to 00:00:00:

> new Date(2017, 4, 10).toISOString()
  "2017-05-10T07:00:00.000Z"
> new Date('2017-04-10').toISOString()
  "2017-04-10T00:00:00.000Z"

Looks like your library is using the new Date(y, m, d) form of the Date constructor.

There is no date-without-time-of-day in JavaScript so you might need to work with strings instead. You could use to_char in the query to get an ISO8601 date string:

select ..., to_char(u.registered, 'YYYY-MM-DD')

That should give you better control over what timezone is applied. Or you could make sure the local timezone for your application is UTC so that you can only worry about timezone issues at the very edges of your application (i.e. display and input).

Convert from JS timestamp to Postgresql Timestamp with time zone

Date.now() only provides the since EPOCH in milliseconds.

You need an ISO date time string. To achieve that, set t like this:

const t = new Date(Date.now()).toISOString();console.log(t);

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)