Get "Time with Time Zone" from "Time Without Time Zone" and the Time Zone Name

Get time with time zone from time without time zone and the time zone name

WARNING: PostgreSQL newbie (see comments on the question!). I know a bit about time zones though, so I know what makes sense to ask.

It looks to me like this is basically an unsupported situation (unfortunately) when it comes to AT TIME ZONE. Looking at the AT TIME ZONE documentation it gives a table where the "input" value types are only:

• timestamp without time zone
• timestamp with time zone
• time with time zone

We're missing the one you want: time without time zone. What you're asking is somewhat logical, although it does depend on the date... as different time zones can have different offsets depending on the date. For example, 12:00:00 Europe/London may mean 12:00:00 UTC, or it may mean 11:00:00 UTC, depending on whether it's winter or summer.

On my system, having set the system time zone to America/Regina, the query

SELECT ('2011-11-22T12:00:00'::TIMESTAMP WITHOUT TIME ZONE) 
                               AT TIME ZONE 'America/Vancouver'

gives me 2011-11-22 14:00:00-06 as a result. That's not ideal, but it does at least give the instant point in time (I think). I believe that if you fetched that with a client library - or compared it with another TIMESTAMP WITH TIME ZONE - you'd get the right result. It's just the text conversion that then uses the system time zone for output.

Would that be good enough for you? Can you either change your SCHEDULES.time field to be a TIMESTAMP WITHOUT TIME ZONE field, or (at query time) combine the time from the field with a date to create a timestamp without time zone?

EDIT: If you're happy with the "current date" it looks like you can just change your query to:

SELECT (current_date + SCHEDULES.time) AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

Of course, the current system date may not be the same as the current date in the local time zone. I think this will fix that part...

SELECT ((current_timestamp AT TIME ZONE USERS.tz)::DATE + schedules.time)
       AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

In other words:

• Take the current instant
• Work out the local date/time in the user's time zone
• Take the date of that
• Add the schedule time to that date to get a TIMESTAMP WITHOUT TIME ZONE
• Use AT TIME ZONE to apply the time zone to that local date/time

I'm sure there's a better way, but I think it makes sense.

You should be aware that in some cases this could fail though:

• What do you want the result to be for a time of 01:30 on a day when the clock skips from 01:00 to 02:00, so 01:30 doesn't occur at all?
• What do you want the result to be for a time of 01:30 on a day when the clock goes back from 02:00 to 01:00, so 01:30 occurs twice?

Get TimeZone offset value from TimeZone without TimeZone name

I need to save the phone's timezone in the format [+/-]hh:mm

No, you don't. Offset on its own is not enough, you need to store the whole time zone name/id. For example I live in Oslo where my current offset is +02:00 but in winter (due to dst) it is +01:00. The exact switch between standard and summer time depends on factors you don't want to explore.

So instead of storing + 02:00 (or should it be + 01:00?) I store "Europe/Oslo" in my database. Now I can restore full configuration using:

TimeZone tz = TimeZone.getTimeZone("Europe/Oslo")

Want to know what is my time zone offset today?

tz.getOffset(new Date().getTime()) / 1000 / 60   //yields +120 minutes

However the same in December:

Calendar christmas = new GregorianCalendar(2012, DECEMBER, 25);
tz.getOffset(christmas.getTimeInMillis()) / 1000 / 60   //yields +60 minutes

Enough to say: store time zone name or id and every time you want to display a date, check what is the current offset (today) rather than storing fixed value. You can use TimeZone.getAvailableIDs() to enumerate all supported timezone IDs.

Difference between timestamps with/without time zone in PostgreSQL

The differences are covered at the PostgreSQL documentation for date/time types. Yes, the treatment of TIME or TIMESTAMP differs between one WITH TIME ZONE or WITHOUT TIME ZONE. It doesn't affect how the values are stored; it affects how they are interpreted.

The effects of time zones on these data types is covered specifically in the docs. The difference arises from what the system can reasonably know about the value:

• With a time zone as part of the value, the value can be rendered as a local time in the client.

• Without a time zone as part of the value, the obvious default time zone is UTC, so it is rendered for that time zone.

The behaviour differs depending on at least three factors:

• The timezone setting in the client.
• The data type (i.e. WITH TIME ZONE or WITHOUT TIME ZONE) of the value.
• Whether the value is specified with a particular time zone.

Here are examples covering the combinations of those factors:

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)

Convert Date/Time for given Timezone - java

For me, the simplest way to do that is:

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

//Here you say to java the initial timezone. This is the secret
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
//Will print in UTC
System.out.println(sdf.format(calendar.getTime()));    

//Here you set to your timezone
sdf.setTimeZone(TimeZone.getDefault());
//Will print on your default Timezone
System.out.println(sdf.format(calendar.getTime()));

Parse date without timezone javascript

The date is parsed correctly, it's just toString that converts it to your local timezone:

let s = "2005-07-08T11:22:33+0000";
let d = new Date(Date.parse(s));

// this logs for me 
// "Fri Jul 08 2005 13:22:33 GMT+0200 (Central European Summer Time)" 
// and something else for you

console.log(d.toString()) 

// this logs
// Fri, 08 Jul 2005 11:22:33 GMT
// for everyone

console.log(d.toUTCString())

Create a Date with a set timezone without using a string representation

using .setUTCHours() it would be possible to actually set dates in UTC-time, which would allow you to use UTC-times throughout the system.

You cannot set it using UTC in the constructor though, unless you specify a date-string.

Using new Date(Date.UTC(year, month, day, hour, minute, second)) you can create a Date-object from a specific UTC time.

Get UTC date time by Date and time Zone name

I would use a library like Day.js for this purpose. You can parse in the date and time in a timezone, convert to UTC, then display in the desired format.

dayjs.extend(utc);
dayjs.extend(timezone);
dayjs.extend(customParseFormat);

function getUTCDateTime(date, time, timeZoneName) {
  const utcDate = dayjs
    .tz(`${date} ${time}`, "YYYY-MM-DD HH:mm", timeZoneName)
    .utc()
    .format("YYYY-MM-DD[T]HH:mm:ss.SSS[Z]");
  return utcDate;
}

Get different timezones when writing and reading row with datetime column?

PostgreSQL stores timestamp with time zone values as UTC and by default displays them in the local time zone. So, if I insert a UTC value via psql …

mydb=# insert into tests (id, test_datetime) values (1, '2021-04-12 11:46:30.8957+00');
INSERT 0 1

… and then retrieve it …

mydb=# select * from tests;
 id |        test_datetime        
----+-----------------------------
  1 | 2021-04-12 05:46:30.8957-06
(1 row)

… it is displayed in my local time zone (currently UTC-6). psycopg2 also returns the value in the local time zone

with engine.begin() as conn:
    result = conn.execute(
        sa.text("SELECT test_datetime FROM tests WHERE id=1")
    ).scalar()
    print(type(result))  # <class 'datetime.datetime'>
    print(result)  # 2021-04-12 05:46:30.895700-06:00

If you want the timezone-aware datetime value to be in UTC then just convert it

    result_utc = result.astimezone(timezone.utc)
    print(result_utc)  # 2021-04-12 11:46:30.895700+00:00

Display datetime with MomentJs without timezone conversion

Use the utc() method of moment to remove the timezone and display everything in universal time.

moment.utc('2015-01-22T16:11:36.36-07:00').format('l LT')

That will display the time as it is in UTC without any timezone offset. If you want to display the time as it was recorded in the user/server timezone you can parse the zone information when you construct a moment instance and have it use the timezone recorded in the parsed string.

moment.parseZone('2015-01-22T16:11:36.36-07:00').format('l LT');

With either of these approaches you should consider labeling the time in some way to reflect the timezone the time corresponds to. Failing to do this could lead to a lot of confusion for the end users.

---

Related Topics