Converting Time in Utc to Pacific Time

Converting Time in UTC to Pacific time

Since it appears you are using rails, you have quite a few options. I suggest reading this article that talks all about time zones.

To convert to PST, both of these are rails-specific methods. No need to re-invent the wheel:

time = Time.parse("07/09/10 14:50")
time.in_time_zone("Pacific Time (US & Canada)")

Hope this helps

UPDATE: rails might try to get smart and give the time you specify as a string a time zone. To ensure that the time parses as UTC, you should specify in the string:

time = Time.parse("07/09/10 14:50 UTC")
time.in_time_zone("Pacific Time (US & Canada)")

Converting UTC to Pacific time using Moment Timezone (javascript)

You can do this in one step:

moment.tz(1412144245453, 'America/Los_Angeles').format('MM/DD/YYYY h:mm a')

OUTPUT: "09/30/2014 11:17 pm"

Also, you had evaluated the times for this timestamp incorrectly. In UTC, this timestamp is October 1st, 2014 6:17:25 AM. The corresponding Pacific time is indeed September 30th, 2014, 11:17:25 PM.

You can check this using a site like epochconverter.com, or in moment.js like so:

moment.utc(1412144245453).format()   // "2014-10-01T06:17:25+00:00"

How to convert the UTC date into PST date in java -simpledateformat

Use java.time and you need no formatter

I recommend that you use java.time, the modern Java date and time API, for your date and time work. I am assuming that by PST you mean North American Pacific Standard Time (America/Vancouver or America/Los_Angeles). Other interpretations exist that are just as valid.

    String testtime = "2021-09-14T21:15:09.863Z";
    
    Instant instant1 = Instant.parse(testtime);
    ZoneId desiredZone = ZoneId.of("America/Los_Angeles");
    ZonedDateTime pstDateTime = instant1.atZone(desiredZone);
    
    System.out.println("PRINTING the TIME in PST: " + pstDateTime);

Output:

PRINTING the TIME in PST:
2021-09-14T14:15:09.863-07:00[America/Los_Angeles]

Oops, we didn’t get PST. We got Pacific Daylight Time or PDT. Wanting PST in September, I doubt that it makes any sense. Unless, of course, you meant Philippines Standard Time or Pitcairn Standard Time.

ISO 8601: Your string is in ISO 8601 format. Instant and the other classes of java.time parse and print ISO 8601 format as their default, that is, without us specifying any formatter. So I didn’t. The output from ZonedDateTime isn’t strictly ISO 8601 format. If you wanted that, you may format the date and time using the builtin DateTimeFormatter.ISO_OFFSET_DATE_TIME:

    System.out.println("PRINTING the TIME in PST: "
            + pstDateTime.format(DateTimeFormatter.ISO_OFFSET_DATE_TIME));

PRINTING the TIME in PST: 2021-09-14T14:15:09.863-07:00

What were you missing?

Apart from using the long outdated and troublesome classes:

• Never hardcode Z as a literal in your format pattern string. In means UTC so you need to parse and format it as an offset, or you will get incorrect result in most cases.
• In particular printing Z after the PST time is wrong. Instead we want offset -08:00 for PST and -07:00 for PDT as in my output above.
• As others have pointed out there is a typo in the time part of your format pattern string both times: HH:mm.sss. It should be HH:mm.ss.SSS for two-digit seconds and three-digit milliseconds. This typo caused the exception that you probably got.
• Don’t rely on PST or other three letter abbreviations for time zones. As I said, they have multiple interpretation. Use a time zone ID like America/Vancouver, so in the region/city format.

Links

• Oracle tutorial: Date Time explaining how to use java.time.
• Wikipedia article: ISO 8601

Excel formula to convert UTC time to Pacific Time

=TEXT(LEFT(A1,14),"0000-00-00 00\:00\:00")-7/24

and format as "m-d-yy h:mm:ss AM/PM"

How to convert UTC to PDT in excel using formula

You could use something like below. It works in 3 parts :

1. Substitute "T" from the value with a space.
2. Take only left part of value for 19 characters that make up the relevant date and time
3. Deduct 7 hours using 7/24 from the time as PDT = UTC - 7 hours

=LEFT(SUBSTITUTE(A2,"T"," "),19) - 7/24

Convert a date from UTC timezone to PST Time zone (including DST factor)

You have 6 hours time difference, so I'm going to assume that you are in the same time zone as Asia/Dacca and have set up my session using:

ALTER SESSION SET TIME_ZONE='Asia/Dacca';

Now, if I create table1 with the data type TIMESTAMP WITH TIME ZONE:

CREATE TABLE table1 (
  name     VARCHAR2(20),
  end_date TIMESTAMP WITH TIME ZONE
);

INSERT INTO table1 ( name, end_date ) VALUES ( 'FIRST', TIMESTAMP '2021-03-15 07:17:16 UTC' );

Then your query (you do not need to use TO_TIMESTAMP_TZ on a column that is already a TIMESTAMP WITH TIME ZONE column):

SELECT TO_CHAR(
         max(end_date) AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

Outputs:

| PST_END_DATE         |
| :------------------- |
| 15-MAR-2021 00:17:16 |

and works!

---

However, if you store end_date using a TIMESTAMP (without time zone):

CREATE TABLE table1 (
  name     VARCHAR2(20),
  end_date TIMESTAMP
);

INSERT INTO table1 ( name, end_date ) VALUES ( 'FIRST', TIMESTAMP '2021-03-15 07:17:16' );

Then:

SELECT TO_CHAR(
         max(end_date) AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

Outputs:

| PST_END_DATE         |
| :------------------- |
| 14-MAR-2021 18:17:16 |

Which replicates your issue.

This is because the database does not know the time zone of the data and will implicitly assume that it is the same as the database/session time zone and we've set that to Asia/Dacca and not UTC. Instead we need to explicitly tell the database to use the UTC time zone for the conversion:

SELECT TO_CHAR(
         FROM_TZ(max(end_date), 'UTC') AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

Which outputs:

| PST_END_DATE         |
| :------------------- |
| 15-MAR-2021 00:17:16 |

---

If your column has the DATE data type:

CREATE TABLE table1 (
  name     VARCHAR2(20),
  end_date DATE
);

INSERT INTO table1 ( name, end_date ) VALUES ( 'FIRST', TIMESTAMP '2021-03-15 07:17:16' );

Then you can use the same query with an added CAST:

SELECT TO_CHAR(
         FROM_TZ(CAST(max(end_date) AS TIMESTAMP), 'UTC') AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

db<>fiddle here

How to convert UTC to PST in Excel for day and time formats?

Excel stores date_times as numbers, where each day is 1, and the decimal part of the number is the time (so 0.25 is 6 hours, and 0.7 is 16 hours + 48 minutes)

Time 0 is the beginning of 1899/12/31, so =today() formatted as a decimal currently shows me as 44730.4556, being 44730 days since 1899 and almost eleven a.m.

Once you have your data in that format it is trivial to E.g.:

• subtract 7 hours = A2 - 7/24
• add 2 days 6 hours and 15 minutes = A2 + 2 + 6/24 + 15/24/60
• find the difference between two date_times

So you want to get the input data into that form, from which point you just use formatting.
To see where you are going, enter in A1 = now(), in B1 =A1, in C1 = B1.

Change the format on B1 - Right Click | Format Cells | Custom | yyyy-mm-dd hh:mm:ss.

Then in C1 try changing to a format you want: dd hh:mm:ss AM/PM.

For a full list of options see https://support.microsoft.com/en-au/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309

I will assume that your data starts at A4, but I can't tell what is actually stored in the cell, as opposed to what it displays.

If your input data is actually already a date_time (what does it look like if you format is as a decimal?) all you need to do is =A4-7/24.

If your input data is actually a string, then separate it into day, hour, minute, second in C, D, E, F.

If single-digit days have leading zeros then just =left(A4,2) then =mid(A4,4,2) etc.

If there are no leading zeros put in column B =find(" ",A4) and point the lefts and mids to that intermediate result.

Then in G put = C4 + D4/24 + E4/24/60 + F4/24/60/60 so you have the input data as an Excel-formatted time.

And subtract the 7 hours difference with =G4-7/24

P.S. If you need to cope with month-ends and DST then you need to add year and month to the data in G - currently it has date_times in January 1899.

---

Related Topics