Data in Timestamp type can be converted to Decimal, Integer or String type. You can also perform the following operations:
This operation adds an increment value to the day.
| Input | Increment | Output |
|---|---|---|
| 2009-12-27 00:30:10 | 5 | 2010-01-01 00:30:10 |
| 2009-12-14 19:15:20 | 5 | 2009-12-19 19:15:20 |
This operation adds an increment value to the hour.
| Input | Increment | Output |
|---|---|---|
| 2011-04-07 23:30:15 | 1 | 2011-04-08 00:30:15 |
| 2010-03-28 17:07:12 | 1 | 2010-03-28 18:07:12 |
This operation adds an increment value to the millisecond.
| Input | Increment | Output |
|---|---|---|
| 2009-04-07 23:30:15 | 10000 | 2009-04-07 23:30:25 |
| 2010-12-10 17:07:12 | 10000 | 2010-12-10 17:07:22 |
This operation adds an increment value to the minute.
| Input | Increment | Output |
|---|---|---|
| 2009-04-07 23:30:15 | 30 | 2009-04-08 00:00:15 |
| 2010-12-10 17:07:12 | 30 | 2010-12-10 17:37:12 |
This operation adds an increment value to the month.
| Input | Increment | Output |
|---|---|---|
| 2009-05-14 09:45:10 | 2 | 2009-07-14 09:45:10 |
| 2009-12-14 16:20:00 | 2 | 2010-02-14 16:20:00 |
This operation adds an increment value to the second.
| Input | Increment | Output |
|---|---|---|
| 2009-03-28 23:30:15 | 45 | 2009-03-28 23:31:00 |
| 2011-04-07 17:07:12 | 45 | 2011-04-07 17:07:57 |
This operation adds an increment value to the year.
| Input | Increment | Output |
|---|---|---|
| 2009-12-14 03:05:10 | 1 | 2010-12-14 03:05:10 |
| 2011-07-01 19:30:00 | 1 | 2012-07-01 19:30:00 |
This operation transforms the time into Boolean type. If the timestamp value is before noon, it is changed to "true", otherwise it is changed to "false".
| Input | Output |
|---|---|
| 2010-12-30 16:51:02 | false |
| 2011-10-09 11:29:46 | true |
This operation shows only the day from the selected field.
| Input | Output |
|---|---|
| 2009-12-14 16:30:00 | 14 |
| 2011-06-23 00:20:10 | 23 |
This operation changes the day value to the first day of each month.
| Input | Output |
|---|---|
| 2010-09-09 10:15:00 | 2010-09-01 10:15:00 |
| 2008-05-24 10:15:00 | 2008-05-01 10:15:00 |
This operation shows only the hours from the selected field.
| Input | Output |
|---|---|
| 2009-11-25 09:28:07 | 9 |
| 2011-01-01 17:56:39 | 17 |
This operation changes the day value to the last day of each month.
| Input | Output |
|---|---|
| 2010-09-09 10:15:00 | 2010-09-30 10:15:00 |
| 2008-05-24 10:15:00 | 2008-05-31 10:15:00 |
This operation changes each value in the selected field to a specified maximum value. If the original value is less than the specified maximum, the result becomes the maximum, otherwise it is unchanged.
| Input | Maximum | Output |
|---|---|---|
| 2011-04-05 07:30:10 | 2011-01-01 12:00:00 | 2011-04-05 07:30:10 |
| 2010-01-01 16:00:00 | 2011-01-01 12:00:00 | 2011-01-01 12:00:00 |
This operation shows only the milliseconds from the selected field.
| Input | Output |
|---|---|
| 2010-09-09 00:30:10 | 0 |
| 2008-05-31 07:31:41 | 0 |
This operation changes each value in the selected field to a specified minimum value. If the original value is greater than the specified minimum, the result becomes the minimum, otherwise it is unchanged.
| Input | Maximum | Output |
|---|---|---|
| 2011-04-05 07:30:10 | 2011-01-01 12:00:00 | 2011-01-01 12:00:00 |
| 2010-01-01 16:00:00 | 2011-01-01 12:00:00 | 2010-01-01 16:00:00 |
This operation shows only the minutes from the selected field.
| Input | Output |
|---|---|
| 2010-05-06 09:28:07 | 28 |
| 2009-07-16 17:56:39 | 56 |
This operation shows only the month from the selected field.
| Input | Output |
|---|---|
| 2009-12-14 07:30:10 | 12 |
| 2011-06-23 21:01:07 | 6 |
This operation shows the month value from the selected field in a long name.
| Input | Output |
|---|---|
| 2009-12-14 07:30:10 | December |
| 2011-06-23 00:15:05 | June |
This operation shows the month value from the selected field in a short name.
| Input | Output |
|---|---|
| 2009-12-14 07:30:10 | Dec |
| 2011-06-23 00:15:05 | Jun |
This operation displays the Null timestamp as a specified value, while the other values remain the same.
| Input | Value | Output |
|---|---|---|
| 2000-01-01 00:00:00 | 2000-01-01 00:00:00 | |
| 2009-01-01 07:30:10 | 2000-01-01 00:00:00 | 2009-01-01 07:30:10 |
This operation transforms the timestamp into Boolean type. If the timestamp value is between noon and midnight, it is changed to "true", otherwise changed to "false".
| Input | Output |
|---|---|
| 2010-12-31 16:51:02 | true |
| 2011-09-20 11:29:46 | false |
This operation returns integers to show which quarter the specified timestamp values belong to.
| Input | Output |
|---|---|
| 2010-11-16 12:30:10 | 4 |
| 2011-03-28 21:00:05 | 1 |
| 2011-05-01 17:12:19 | 2 |
| 2011-08-20 00:30:00 | 3 |
This operation returns strings to show which quarter the specified timestamp values belong to.
| Input | Output |
|---|---|
| 2010-11-16 12:30:10 | Q4 |
| 2011-03-28 21:00:05 | Q1 |
| 2011-05-01 17:12:19 | Q2 |
| 2011-08-20 00:30:00 | Q3 |
This operation shows only the seconds from the selected field.
| Input | Output |
|---|---|
| 2011-03-28 09:28:07 | 7 |
| 2009-07-16 17:56:39 | 39 |
This operation enables you to set the day value in the selected field.
| Input | Day | Output |
|---|---|---|
| 2010-01-01 07:30:10 | 5 | 2010-01-05 07:30:10 |
| 2008-01-01 21:10:00 | 5 | 2008-01-05 21:10:00 |
This operation enables you to set the hour value in the selected field.
| Input | Hour | Output |
|---|---|---|
| 2011-12-09 00:30:10 | 20 | 2011-12-09 20:30:10 |
| 2009-03-15 07:31:41 | 20 | 2009-03-15 20:31:41 |
This operation enables you to set the millisecond value in the selected field. If the specified value is not big enough to calculate into seconds, minutes or hours, the result may look the same with the original value. However, the hidden millisecond value can affect comparing and sorting.
| Input | Value | Output |
|---|---|---|
| 2011-12-09 00:30:10 | 1 | 2011-12-09 00:30:10 |
| 2009-03-15 07:31:41 | 1 | 2009-03-15 07:31:41 |
This operation enables you to set the minute value in the selected field.
| Input | Value | Output |
|---|---|---|
| 2011-12-09 00:30:10 | 1 | 2011-12-09 00:01:10 |
| 2009-03-15 07:31:41 | 1 | 2009-03-15 07:01:41 |
This operation enables you to set the month value in the selected field.
| Input | Month | Output |
|---|---|---|
| 2011-01-01 14:00:00 | 1 | 2011-01-01 14:00:00 |
| 2009-07-17 23:30:00 | 1 | 2009-01-17 23:30:00 |
This operation enables you to set the second value in the selected field.
| Input | Value | Output |
|---|---|---|
| 2011-12-09 00:30:10 | 1 | 2011-12-09 00:30:01 |
| 2009-03-15 07:31:41 | 1 | 2009-03-15 07:31:01 |
This operation enables you to set the year value in the selected field.
| Input | Year | Output |
|---|---|---|
| 2011-01-01 12:30:00 | 2011 | 2011-01-01 12:30:00 |
| 2008-07-17 19:00:00 | 2011 | 2011-07-17 19:00:00 |
This operation subtracts a decrement value from the day.
| Input | Decrement | Output |
|---|---|---|
| 2009-12-27 16:30:00 | 5 | 2009-12-22 16:30:00 |
| 2009-12-05 05:30:00 | 5 | 2009-11-30 05:30:00 |
This operation subtracts a decrement value from the milliseconds.
| Input | Decrement | Output |
|---|---|---|
| 2009-04-26 23:30:15 | 5 | 2009-04-26 18:30:15 |
| 2011-10-02 17:07:12 | 5 | 2011-10-02 12:07:12 |
This operation subtracts a decrement value from the milliseconds. If the decrement value is not big enough to affect seconds, minutes or hours, the result may look the same with the original value. However, the hidden millisecond value can affect comparing and sorting.
| Input | Decrement | Output |
|---|---|---|
| 2009-04-26 23:30:15 | 30 | 2009-04-26 23:30:15 |
| 2011-10-02 17:07:12 | 30 | 2011-10-02 17:07:12 |
This operation subtracts a decrement value from the minutes.
| Input | Decrement | Output |
|---|---|---|
| 2009-04-26 23:30:15 | 30 | 2009-04-26 23:00:15 |
| 2011-10-02 17:07:12 | 30 | 2011-10-02 16:37:12 |
This operation subtracts a decrement value from the month.
| Input | Decrement | Output |
|---|---|---|
| 2009-12-14 22:30:20 | 1 | 2009-11-14 22:30:20 |
| 2009-01-22 08:15:07 | 1 | 2008-12-22 08:15:07 |
This operation subtracts a decrement value from the seconds.
| Input | Decrement | Output |
|---|---|---|
| 2011-03-28 23:30:15 | 15 | 2011-03-28 23:30:00 |
| 2010-12-12 16:00:00 | 15 | 2010-12-12 15:59:45 |
This operation subtracts a decrement value from the year.
| Input | Decrement | Output |
|---|---|---|
| 2009-12-27 00:30:10 | 2 | 2007-12-27 00:30:10 |
| 2011-02-05 16:20:40 | 2 | 2009-02-05 16:20:40 |
This operation calculates the timestamp value into milliseconds since the start of the day.
| Input | Output |
|---|---|
| 2010-10-15 12:30:00 | 45000000 |
| 2008-05-24 10:15:00 | 36900000 |
This operation calculates the timestamp value into seconds since the start of the day.
| Input | Output |
|---|---|
| 2010-10-15 12:30:00 | 45000 |
| 2008-05-24 10:15:00 | 36900 |
This operation changes the hour, minute and second values to zero.
| Input | Output |
|---|---|
| 2009-12-27 01:10:55 | 2009-12-27 00:00:00 |
| 2009-01-05 17:59:30 | 2009-01-05 00:00:00 |