Use TIMESTAMP instead of TIME
CREATE TABLE table_2
(column_1 INTEGER,
column_2 TIMESTAMP);
INSERT INTO table_2 SELECT * FROM table_1;
SELECT * FROM table_2
ORDER BY column_2;
column_1 column_2
-------- --------------------------
0 2011-11-07 00:00:00.000000
1 2011-11-07 01:00:00.000000
2 2011-11-07 02:00:00.000000
3 2011-11-07 03:00:00.000000
4 2011-11-07 04:00:00.000000
5 2011-11-07 05:00:00.000000
6 2011-11-07 06:00:00.000000
7 2011-11-07 07:00:00.000000
8 2011-11-07 08:00:00.000000
9 2011-11-07 09:00:00.000000
10 2011-11-07 10:00:00.000000
11 2011-11-07 11:00:00.000000
12 2011-11-07 12:00:00.000000
13 2011-11-07 13:00:00.000000
14 2011-11-07 14:00:00.000000
15 2011-11-07 15:00:00.000000
16 2011-11-07 16:00:00.000000
17 2011-11-07 17:00:00.000000
18 2011-11-07 18:00:00.000000
19 2011-11-07 19:00:00.000000
20 2011-11-07 20:00:00.000000
21 2011-11-07 21:00:00.000000
22 2011-11-07 22:00:00.000000
23 2011-11-07 23:00:00.000000
Different languages use different collation sequences, and you might need to alter your system collation to ensure that the ORDER BY clauses you specify in your queries sort correctly.
You can specify the default collation for a user using the CREATE USER or MODIFY USER statement, or you can specify collation for the duration of a session using the SET SESSION COLLATION statement. Otherwise, collation for a session is determined by the logon client system. The direction of the sort can be controlled by including the DESC (descending) or ASC (ascending) sort option in the SQL request.
Teradata Database supports ASCII, EBCDIC, and MULTINATIONAL collating sequences. If MULTINATIONAL is in effect, your collation will be one of the European (diacritical) or Kanji sort sequences described in “International Sort Orders” on page 212.
The following topics explain the results of ORDER BY as affected by whether character string expressions have the CASESPECIFIC or NOTCASESPECIFIC attribute.
If character strings are to be sorted NOT CASESPECIFIC, which is the default in Teradata session mode, only lowercase simple letters (for example, the characters a … z in the Latin alphabet) are converted to uppercase before a comparison or sorting operation is done.
The important thing to understood is that case differences are ignored in NOT CASESPECIFIC collations. In other words, the case of the characters is not critical for NOT CASESPECIFIC collation.
Any non-Latin single‑byte character, any multibyte character, and any byte indicating a transition between single‑byte characters and multibyte characters is excluded from this function.
If the character strings are to be sorted CASESPECIFIC, which is the default in ANSI session mode, then the case of the characters is critical for collation.
For Kanji1 character data in CASESPECIFIC mode, the letters in any alphabet that uses casing, such as Latin, Greek, or Cyrillic, are considered to be matched only if they are the identical letters and have the same case.
The system does not consider FULLWIDTH and HALFWIDTH characters to be matched whether in CASESPECIFIC or NOT CASESPECIFIC mode.
Teradata Database uses one of four server character sets to support Japanese characters:
Notice:
KANJI1 support is deprecated. KANJI1 is not allowed as a default character set. The system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible.
If you are at a Japanese character site and you want to ensure that sorting of character data is as close as possible to that of the client, you should set your session collation (see “SET SESSION COLLATION” in SQL Data Definition Language) as follows:
For character data stored on the Teradata platform as either KanjiSJIS or Unicode, the CHARSET_COLL collation provides the collation that is closest to sorting on the client.
For Kanji1 character data, the ASCII collation provides a collation similar to that of the client, assuming the session character set is KanjiSJIS_0S, KanjiEUC_0U, or something very similar. However, ASCII collation does not sort like the client if the data is stored on the Teradata platform using the ShiftJIS or Unicode server character sets.
Note: Each character set requires a different definition for its MULTINATIONAL collation to collate properly.
Teradata Database handles character data collation for the different Japanese server character sets as follows:
ASCII collation collates Kanji1 data in binary order, but handles case matching of single‑byte or HALFWIDTH Latin characters (see International Character Set Support for details). This matches collation on the client for KanjiSJIS_0S sessions, is close for KanjiEUC_0U, and is only reasonably close for double‑byte data for KanjiEBCDIC sessions. Note that KanjiEUC_0U puts code set 3 after code set 1 rather than before code set 1 as KanjiEBCDIC does.
Note that it is not true that multibyte Kanji1 characters remain in the client encoding for KanjiEUC_0U sessions.
For details, see International Character Set Support and SQL Data Types and Literals.
MULTINATIONAL collation is enabled at the user level via the COLLATION option of the CREATE USER or MODIFY USER statement. If a collation is not specified, the default is HOST; that is, the standard sort ordering of the client system (EBCDIC for IBM clients, ASCII for all others).
You can override the default at the session level by issuing the SET SESSION COLLATION statement.
When MULTINATIONAL collation is in effect, the default collation sequence is determined by the collation setting installed at system start-up. Also see International Character Set Support.
Each international sort sequence is defined by program constants and no check is made to ensure that collation is compatible with the character set of the current session. The choice of sequence is controlled by your database administrator. The programmed character sequences cannot be changed.
The sort order uses a two-level comparison that involves the following rules.
In ANSI session mode, the default is CASESPECIFIC and you must explicitly specify NOT CASESPECIFIC to change this.
For details, see SQL Data Types and Literals.
When these rules are applied, the words “abbey,” “Active,” and “adage” are returned in this order,
abbey Active adage
and the names Muller, Handl, Böckh, Mueller, Händl, Bohr, Bock, and Müller are ordered as:
Bock Böckh Bohr Handl
Händl Mueller Muller Müller
Equivalence classes and the ordering of diacritical characters in each class are shown in this table. The listed classes are those with characters that have diacritical forms.
European Sort Order |
|||||
A |
C |
E |
I |
N |
O |
a A à À á Á â Â ã Ã ä Ä |
c C ç Ç |
e E è È é É ê Ê ë Ë |
i I ì Ì í Í î Î ï Ï |
n N ñ Ñ |
o O ò Ò ó Ó ô Ô õ Õ ö Ö o O |
S |
U |
Y |
AE |
O slash |
A ring |
s S β |
u U ù Ù ú Ú û Û (U tilde) ü Ü |
y Y ÿ Ÿ |
æ Æ |
ø Ø |
å Å |