在Informix中转换日期 [英] Cast Date in Informix

问题描述

我从来没有使用过Informix，我试图写一个将在过去365天内返回记录的查询。

I have never used Informix before and I'm trying to write a query that will return records over the last 365 days.

这是我一直在查询尝试使用：

Here is the query I have been trying to use:

Select * from Visit where vis_mod_dt between today-365 and today;

即使我知道有过去365天的数据，也不会返回任何记录。我猜，vis_mod_dt不是一个真正的日期列，虽然它显示为'12 / 31/1899'我试图使用：

That returns no records even though I know that there is data for the last 365 days. I am guessing that the vis_mod_dt in not a true date column, although it displays as '12/31/1899' I have tried to cast this column using:

select * from visit where date(vis_mod_dt) between today-365 and today;

这仍然不会返回任何数据。

This still returns no data.

任何想法？

推荐答案

Informix DATE格式

日期1899-12-31对应于内部日期表示为零（0）。也就是说，在内部，Informix将DATE值存储在一个4字节的整数中，并计算自1899-12-31以来的天数，因此第1天为1900-01-01（并且因为它知道1900不是闰年，第60天是1900-03-01）。

Informix DATE format

Be aware that the date 1899-12-31 corresponds to the internal date representation of zero (0). That is, internally, Informix stores DATE values in a 4-byte integer, and counts the number of days since 1899-12-31, so Day 1 was 1900-01-01 (and since it knows that 1900 was not a leap year, Day 60 was 1900-03-01).

这个事实让我担心你的表中有什么。

That fact makes me worry about what is in your table. However, if the data in your table cannot be converted to a DATE upon request, normally you would get an error.

使用DB-Access和Info / Tables选项或使用DB-Schema建立模式是很明智的：

It would be sensible for you to establish the schema either using DB-Access and the Info/Tables option, or use DB-Schema:

dbschema -d dbase -t visit

使用'TODAY-365'和'TODAY'的查询表达式应该可以正常工作 - 如果有数据要选择。

The query expressions using 'TODAY-365' and 'TODAY' should work fine - if there is data to select.

有一个环境变量DBDATE可能需要设置才能得到事情工作 - 将字符串表示转换为日期。由于您可能位于英国（从您的图标），那么您可能需要并需要将DBDATE的值设置为：

There is an environment variable, DBDATE, that you may need to set to get things to work - to convert from a string representation to dates. Since you are probably based in the UK (from your icon), then you may want and need to set the value of DBDATE to:

export DBDATE=DMY4/

这说明日期包括日，月， - 数字年，/用作首选分隔符。你不会惊讶地得知，假定的默认值通常是MDY4 /，对于美国格式;我习惯使用Y4MD-，所以我看到DATE的值与DATETIME YEAR TO DAY相同，这是日期的ISO 8601：2004表示法。 （它有很多好处：它是明确的，天真的排序算法按照日期顺序排序这些日期。）实际上在IDS的后台有很多机制（IBM Informix Dynamic Server - 我假设是你的DBMS使用;有一些备选方案也是Informix DBMS），使得带有2位数日期的字符串通常会被正确转换（但是它们是不明确的和不合需要的），并且除了'/'之外的分隔符将在输入上被识别，将用于输出（将DATE转换为字符串时）。

This says that dates consist of the day, the month, a 4-digit year and the '/' is used as the preferred separator. You won't be surprised to learn that the presumed default value is usually 'MDY4/', for US format; I use 'Y4MD-' habitually, so I see DATE value the same as DATETIME YEAR TO DAY, which is the ISO 8601:2004 notation for a date. (It has many benefits: it is unambiguous, and naive sorting algorithms sort such dates into date order.) There's actually a lot of mechanism in the background in IDS (IBM Informix Dynamic Server - which, I assume, is the DBMS that you are using; there are some alternatives that are also Informix DBMS) such that strings with 2-digit dates will usually be converted correctly (but they are ambiguous and undesirable), and separators other than '/' will be recognized on input, but the slash will be used on 'output' (when converting DATE to string).

如果这里没有任何帮助，建议您修改问题以包括：

If what is here does not help, then I recommend editing your question to include:

1. 表格架构。


2. 您认为应该选择几（2-4）行数据， t。


3. 平台和版本信息。它可以帮助将版本降低到IDS 11.50.FC4W1的详细程度;偶尔也很重要。

如果您的表格很大（许多列），请尝试选择键列（ vis_mod_dt 是最重要的一列）。理想情况下，在显示中不需要任何滚动条。
确保您不包括任何敏感信息。

If your table is big (many columns), try to select the key columns (vis_mod_dt is by far the most important one). Ideally, you won't need any scroll bars in the display. Make sure you don't include any sensitive information.

如果您注意我问的问题，我会帮助您。我不能帮助你，如果你不注意我问的问题。

• 表模式是什么？什么是输出：

• What is the table schema? What is the output from:

SELECT t.tabid, t.tabname, c.colno, c.colname, c.coltype, c.collength
  FROM "informix".systables AS t, "informix".syscolumns AS c
 WHERE t.tabid = c.tabid
   AND t.tabname = "visit"
 ORDER BY t.tabid, c.colno;

您可以从：

What do you get from:

SELECT TODAY, TODAY-365 FROM "informix".systables WHERE tabid = 1;

您是否设置了环境变量DBDATE？如果是，它的值是什么？

Do you have the environment variable DBDATE set? If so, what is its value?

注意：如果您无法复制' '粘贴上面的查询，那么你可能不需要在系统目录中包含引用的informix。但是，如所写的，查询将在任何现有的Informix数据库OnLine 5.x，SE 5.x或7.x，IDS 7.x，XPS 8.x，IDS 9.x或10.x或11上工作。 x和任何模式的数据库（unlogged，logged，MODE ANSI）。我将使用JOIN符号，除了一些旧版本不支持它 - 虽然你必须是非常老的版本，这是一个问题。

Note: if you cannot copy'n'paste the queries above, then you probably do not need to include the quoted '"informix".' attributes on the system catalog; however, as written, the queries will work on any extant Informix database - OnLine 5.x, SE 5.x or 7.x, IDS 7.x, XPS 8.x, IDS 9.x or 10.x or 11.x - and any mode of database (unlogged, logged, MODE ANSI). I'd use the JOIN notation except that some of the older versions don't support it - though you have to be on very old versions for that to be a problem.

这篇关于在Informix中转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！