Firebird – generate time/date/timestamp values
Posted by jpluimers on 2017/10/31
I needed the script in [WayBack] Firebird – generate time/date/timestamp values to reproduce a problem in Database Workbench <= 5.3.2 with exporting TIME and TIMESTAMP values to Excel xlsx format: the time portion got lost so
- TIME columns all became a date
1900-01-00(I think the00is because of the infamous 1900 problem that also makes this hard: Excel worksheet function – How do I get the weekday name of a date?) - TIMESTAMP columns all got the midnight timestamp of the date value specified
The bug is fixed so will appear in the next beta and release versions: [WayBack] 0001506: Timestamp export to Excel XLSX only shows date, Time fails, CSV shows both date and time – Bug & Feature Tracker @ Upscene.com
–jeroen
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with | |
| recursive | |
| sequence(n) as ( | |
| — When you select more than 1024 values, this error occurs: | |
| — Error while fetching data: Too many concurrent executions of the same request | |
| select 0 — start | |
| from rdb$database | |
| union all | |
| select sequence.n + 1 | |
| from sequence | |
| where sequence.n < 1023 — finish | |
| ) | |
| select sequence.n | |
| , sequence.n * 59 + time '00:00:00' as nTime — 59 is prime, so we get seconds, minutes and hours | |
| , sequence.n * 13 + date '1899-12-30' as nDate | |
| , sequence.n * 13.059 + timestamp '1899-12-30 00:00:00.000' as nTimeStamp | |
| from sequence | |
| order by sequence.n |
The Wiert Corner - irregular stream of stuff 
Leave a comment