Timestamp
Navigation
Online convertor (http://www.raditha.com/mysql/mysql2pgsql.php)
Making up the numbers
Basic Administration
Strange characters
Links (http://www.raditha.com/mysql/links.php)
Date and Time
Judging by the number of messages posted daily on various newsgroups and mailing lists, date and time fields are one of the most confusing for developers. Mysql as usual has done their best to increase this confusion. The behaviour of date time fields even differ between different versions of that database.
In postgresql the types are simply TIMESTAMP, DATE, TIME and INTERVAL. Just to ensure that you don't get the hang of it too easily, there is a WITHOUT TIME ZONE qualifier that can be used on all but the interval field.
Mysql has two additional data types named DATETIME and YEAR thankfully it does not have the WITHOUT TIME ZONE qualifier but as we discovered with string columns, some of the six types exibit nearly identical behaviour.
Mysql will accept almost any delimiter to separate the year, month, date etc from these fields. In fact it will also accept strings without any delimiters at all provided that the string makes sense. Dates that don't make sense will usually be converted to 0 (0000-00-00 00:00:00),
| Comparision of Date and Time types. | |||||
| mySQL | Postgresql | ||||
|---|---|---|---|---|---|
| Type | Range | Description | Type | Range | Description |
| datetime | 1000-01-01 to 9999-12-31 | Both date and time | N/A | N/A | N/A |
| timestamp | 1970-01-01 to 2037-12-31 | Both date and time | timestamp | 4713 BC to 5874897 AD | Both date and time |
| date | 1000-01-01 to 9999-12-31 | Date only | date | 4713 BC to 32767 AD | Date only. |
| time | -838:59:59 to '838:59:59 | Elapsed time or time of day | time | 00:00:00.00 to 23:59:59.99 | Time of day |
| year | 1901 to 2155 | Year | N/A | N/A | N/A |
| N/A | N/A | N/A | interval | -178000000 years to 178000000 years | Time intervals |
So it would seem that we could use the postgresql timestamp field in place of the datetime field in mysql however mysql does not have an answer for the interval type. Though the year and time types of mysql do not have a direct counterpart in the other system, their input and output can be simulated with appropriate formatting .
