需要在某个时区读取纪元时间，然后将其转换为用户时区

I have 2 webapps, 1 of them on cloud is "master", to which I need to match dates in the 2nd webapp "child".

我有2个webapps,其中1个在云端是“master”,我需要在第2个webapp“child”中匹配日期。

Master (1st webapp, cloud) is showing date in IST, Asia/Kolkata which it reads from sql machine sitting in EST timezone.

Master(第一个webapp,cloud)在IST,Asia / Kolkata中显示日期,它从位于EST时区的sql机器读取。

Child (2nd webapp) reads it's data from Elasticsearch where a java feeder picks up the sql data and pushes it to Elasticsearch as it is, without any conversion.

子(第二个webapp)从Elasticsearch读取数据,其中一个java传送器获取sql数据并按原样将其推送到Elasticsearch,无需任何转换。

When I try to read this Elasticsearch data in my webapp (child)

当我尝试在我的webapp(孩子)中读取此Elasticsearch数据时

...
{
    "_index": "log_event_2016-05-05",
    "_type": "log_event",
    "_id": "65708004",
    "_score": null,
    "_source": {
        "task_name": "kn_cvs_test",
        "task_start_time": "2016-05-05T19:05:05.000-07:00",
        "task_end_time": "2016-05-05T19:05:06.000-07:00",
        "started_by": "Schedule \"10Minutes\"",
        "log_datetime": 1462475106000,
        "dw_insert_dt": "2016-05-05T16:40:54.000-07:00"
    },
    "sort": [
        1462475106000
    ]
}, {
    "_index": "log_event_2016-05-05",
    "_type": "log_event",
    "_id": "65708005",
    "_score": null,
    "_source": {
        "task_name": "kn_cvs_test",
        "task_start_time": "2016-05-05T18:55:08.000-07:00",
        "task_end_time": "2016-05-05T18:55:11.000-07:00",
        "started_by": "Schedule \"10Minutes\"",
        "log_datetime": 1462474511000,
        "dw_insert_dt": "2016-05-05T16:40:54.000-07:00"
    },
    "sort": [
        1462474511000
    ]
}
...

the dates in my webapp and the cloud does not match. Please correct me if I am wrong. Since Sql is storing dates in EST, "America/New_York", Momentjs should 1st read the data = 1462475106000 in EST and then apply the user timezone which is IST, "Asia/Kolkata". Is this correct?

我的webapp和云中的日期不匹配。如果我错了,请纠正我。由于Sql在EST中存储日期,“America / New_York”,Momentjs应首先读取EST中的数据= 1462475106000,然后应用用户时区IST,“Asia / Kolkata”。它是否正确?

//Timestamp column in table
//data  = 1462475106000
$scope.getMeData = function(data) {
    var dFormat = "YYYY-MM-DD hh:mm:ss A";
    moment.tz.setDefault("America/New_York");       
    return moment.tz(data, "Asia/Kolkata").format(dFormat); 
}

Note: 1462475106000 is the 1st entry in both table

注意:1462475106000是两个表中的第一个条目

I am putting up a plunker here. Please help me figure out what could be going wrong and how can I match dates in both the webapps (taking cloud as reference).

我在这里贴了一个傻瓜。请帮我弄清楚可能出现的问题以及如何在两个webapps中匹配日期(以云为参考)。

Update

Java feeder runs a sql query to fetch all the needed columns. Here is how log_datetime is fetched. Is this the correct way to fetch?

Java feeder运行sql查询以获取所有需要的列。以下是获取log_datetime的方法。这是正确的获取方式吗?

(task_end_time - to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*3600*1000 AS "log_datetime"

So I am assuming when it fetches data Daylight saving information is not considered and I am missing out this info too. So on UI side i'll check for isDST() and do a +5:00 hrs or +4:00 hrs depending on it, as date in sql is stored in America/New_York. Plunker with UI fix

所以我假设它何时获取数据不考虑夏令时信息,我也错过了这个信息。所以在UI方面,我将检查isDST()并根据它进行+5:00小时或+4:00小时,因为sql中的日期存储在America / New_York中。带有UI修复的Plunker

2 个解决方案

#1

There's only one Epoch time anywhere at a time. It's an absolute value.

一次只有一个*时间。这是绝对的价值。

A value that approximates the number of seconds that have elapsed since the Epoch. A Coordinated Universal Time name (specified in terms of seconds (tm_sec), minutes (tm_min), hours (tm_hour), days since January 1 of the year (tm_yday), and calendar year minus 1900 (tm_year)) is related to a time represented as seconds since the Epoch, according to the expression below.

Ref http://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap04.html#tag_04_15

Epoch time is measured from the 01-01-1970 00:00:00 UTC. That's an absolute instant in time, it's not relative to timezone as the timezone UTC is in definition of the epoch.

纪元时间是从01-01-1970 00:00:00 UTC测量的。这是一个绝对的时刻,它与时区无关,因为时区UTC是对时代的定义。

Whenever you see a number for a date such as your example 1462475106000, it should be assumed to be in UTC/GMT. It's not a timezone specific value. Since the time is Unix based number, by epoch definition it is time passed since epoch (in UTC), making it absolute time.

每当您看到日期编号(如示例1462475106000)时,应假定为UTC / GMT。它不是特定于时区的值。由于时间是基于Unix的数字,因此按时期定义它是从纪元(UTC)开始的时间,使其成为绝对时间。

(01-01-1970 00:00:00 UTC)(epoch) + 1462475106000 = Time in UTC

A date in string is a different matter entirely. It should include the timezone offset with the date part. Date part would be considered to be timezone specific value. Adding timezone offset to it would convert it to absolute value in UTC. Without timezone information a string date has no absolute value (Point in time).

字符串中的日期完全不同。它应包括与日期部分的时区偏移量。日期部分将被视为时区特定值。向其添加时区偏移量会将其转换为UTC中的绝对值。没有时区信息,字符串日期没有绝对值(时间点)。

2016-05-05T18:55:08.000-07:00 = (2016-05-05T18:55:08.000) + (07:00 hrs) UTC
or
date + timezone offset = date - offset hrs UTC

Databases don't store dates in any timzzone. They store absolute Unix time value. Timezone setting in databases are set so that the output of the query in string is shown in that timezone format. This setting only specifies the output date format not the value. Since the value is absolute it's same timezone independent.

数据库不会在任何timzzone中存储日期。它们存储绝对的Unix时间值。设置数据库中的时区设置,以便以字符串格式显示字符串中查询的输出。此设置仅指定输出日期格式而不是值。由于该值是绝对值,因此它与时区无关。

This setting also helps database in determining the timezone of the date value that a user inserts if he misses the timezone offset. If the user tries to insert a string in date column without timezone database would try to default the string to database timezone setting.

此设置还有助于数据库确定用户在错过时区偏移时插入的日期值的时区。如果用户尝试在没有时区数据库的日期列中插入字符串,则会尝试将字符串默认为数据库时区设置。

So 1462475106000 is a value in UTC as it's Unix time. It's not in EST.

因此,1462475106000是UTC中的值,因为它是Unix时间。它不在美国东部时间。

If you need date to be in EST then use the date in string format not in number format as number format is always UTC.

如果您需要日期在EST中,则使用字符串格式的日期而不是数字格式,因为数字格式始终为UTC。

moment.tz.setDefault("America/New_York");//sets the output date format in EST

Above code will have no effect as it's overwritten by moment.tz(data, "Asia/Kolkata")

上面的代码没有效果,因为它被moment.tz覆盖(数据,“亚洲/加尔各答”)

Now considering the first entry in the first screenshot.

现在考虑第一个截图中的第一个条目。

2016-05-06T04:35:06.000+5:30 (IST) = 2016-05-05T11:05:06.000-00:00 (UTC)

Comparing it with the second screenshot, since the time difference between the two is 4 hours, second one should be in +01:30 timezone, if they both are the same values in absolute time.

将其与第二个屏幕截图进行比较,因为两者之间的时差为4小时,如果它们在绝对时间内都是相同的值,则第二个应该在+01:30时区。

2016-05-06T12:35:06.000+1:30 = 2016-05-05T11:05:06.000-00:00 (UTC)

However in the child the value is

但是孩子的价值在于

2016-05-05T19:05:06.000-07:00 = 2016-05-06T02:05:06.000-00:00 (UTC)

Which is not the same value as both above. Interesting fact is that only date part in the child 2016-05-05T19:05:06.000 when added IST offset +05:30would become the date part in second screenshot.

这与上述两者的价值不同。有趣的事实是,只有日期部分在孩子2016-05-05T19:05:06.000中,当添加IST偏移+05:30时将成为第二个屏幕截图中的日期部分。

2016-05-06T12:35:06.00 - 2016-05-05T19:05:06.000 = 5:30 (IST offset)

Leaving that aside your Java feeder code to ElasticSearch is probably culprit here.

将Java代理代码留给ElasticSearch放在一边可能是罪魁祸首。

It's not writing the correct date-timezone combination to ElasticSearch. Try using the overloaded method getDate() getDate(int columnIndex, Calendar cal) in JDBC.

它没有将正确的日期 - 时区组合写入ElasticSearch。尝试在JDBC中使用重载方法getDate()getDate(int columnIndex,Calendar cal)。

Sometimes JDBC driver misses timezone information, causing the date to be stored in default database timezone, using Calendar would cure that.

有时JDBC驱动程序会错过时区信息,导致日期存储在默认数据库时区中,使用Calendar可以解决这个问题。

Same goes for writing dates as well.

写日期也是如此。

void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException

void setDate(int parameterIndex,Date x,Calendar cal)抛出SQLException

#2

You just need to convert dates from America/New York to Asia/Kolkata (Asia/Calcutta)?

您只需将日期从美国/纽约转换为亚洲/加尔各答(亚洲/加尔各答)?

var newYork    = moment.tz("date-from-database", "America/New_York");
var calcutta   = newYork.clone().tz("Asia/Calcutta");

Example

var newYork    = moment.tz("2016-05-06 12:00:00", "America/New_York");
var losAngeles = newYork.clone().tz("America/Los_Angeles");
var london     = newYork.clone().tz("Europe/London");
var Kolkata     = newYork.clone().tz("Asia/Kolkata"); 
var dFormat = "YYYY-MM-DD hh:mm:ss A";
console.log(newYork.format(dFormat),losAngeles.format(dFormat),london.format(dFormat),Kolkata.format(dFormat));

Fiddle

#1